467,925 Members | 1,912 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,925 developers. It's quick & easy.

PHP retrieve the values of only one column

I want to only retrieve the values of one column in mysql and use these values to plot a graph. I know how to echo these values. However, I am unable to write them seperately. Let's say I have a table called cars. It has a column for car vin and a column for brand and a column for price. I just want to retrieve the column for the price and use these numbers to plot a bar graph.
So the X-axis shows bar 1, 2, 3 ... and the bar length shows the price. I know how to graph but I cannot retrieve the numbers from that column. Please help.
Sep 9 '07 #1
  • viewed: 4746
Share:
14 Replies
Atli
Expert 4TB
Hi.

MySQL will only return those columns you put into the SELECT statement. Unless you use the wild card character (*), in which case it will return all of them.

For example:
Expand|Select|Wrap|Line Numbers
  1. SELECT column1, column2 FROM myTable;
  2.  
So to do this in PHP:
Expand|Select|Wrap|Line Numbers
  1. # Connect
  2. $db = mysql_connect("host", "user", "passwd") or die(mysql_error());
  3. mysql_select_db("mydb", $db) or die(mysql_error());
  4.  
  5. # Execute query
  6. $sql = "SELECT col1 FROM myTbl";
  7. $result = mysql_query($sql, $db) or die(mysql_error());
  8.  
  9. # Display results
  10. while($row = mysql_fetch_assoc($result)) {
  11.   $rowNum++;
  12.   echo "Row $rowNum: ". $row['col1'];
  13. }
  14.  
  15. # Free resources
  16. mysql_free_result($result);
  17. mysql_close($db);
  18.  
Sep 9 '07 #2
Thanks Ati
I got it working to display or echo the results as you see:
Row 1: 15Row 2: 18Row 3: 26Row 4: 14Row 5: 54Row 6: 31
However, I want to use these values in another array. How do I retrieve them.
my array is like this:

$data=(1=> somenumber1, somenumber2, somenumber3 ....);
somenumber is really a number. I just wrote as somenumber
How do I replace these "somenumbers" with the value of that column that I echoed.
Like somenumber1 should be the value of Row 1
and somenumber2 should be the value of Row 2
Remember: I want to use these values outside the while loop.
Thanks for your help.
Sep 9 '07 #3
Atli
Expert 4TB
You can do what ever you can think of with the data. The echo is just to demonstrate.

Try changing the line with the echo into either of these, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. # To give each ID a numeric index
  2. $data[] = $row['myID'];
  3.  
  4. # To give each ID an associative index
  5. $data["ID_number_$rowNum"] = $row['myID'];
  6.  
Sep 9 '07 #4
Hi Atli
Forgive my ignorance but after using $data[]=$row['myID'};
then how do use them.
for example I want to use row 1 through 6 how
do I represent them?
like $myarray=array(1=>$row[0], $row[1],....)
I am really stuck.
Thanks again for your help.

You can do what ever you can think of with the data. The echo is just to demonstrate.

Try changing the line with the echo into either of these, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. # To give each ID a numeric index
  2. $data[] = $row['myID'];
  3.  
  4. # To give each ID an associative index
  5. $data["ID_number_$rowNum"] = $row['myID'];
  6.  
Sep 9 '07 #5
Hi Atli
I finally figured out to write the data. However, now I wonder how I can pass these numbers to the page that plots the graph.
I should make it that it passes these values that are around 21 distinct values to be passed to the page that plot the graph.
like:
$data=array(1=>$number1, $number2,......,$number21);
Should use the above code in the same page as the graphing page. I tried it did not work.
Should I call the plotting page and pass these values to that page. I don't know how.
thanks a lot.
Sep 9 '07 #6
Atli
Expert 4TB
Arrays are easy to use.

You can create the in several ways. For example:
Expand|Select|Wrap|Line Numbers
  1. # You can enter the data into it as you create it
  2. # in which case each element will be given a numeric index
  3. # starting from 0.
  4. $myArr = array("value1", "value2", "value3");
  5.  
  6. # You can use this like this
  7. echo "First element: ". $myArr[0];
  8. echo "Second element: ". $myArr[1];
  9.  
  10.  
  11. # You can also give the elements names instead
  12. # of the numeric indexes.
  13. $myArr = array("first" => "value1", "second" => "value2");
  14.  
  15. # Which you can use like this
  16. echo "First element: ". $myArr["first"];
  17. echo "Second element: ". $myArr["second"];
  18.  
  19. # It is also possible to add the data after
  20. # the array has been created, like I did in the
  21. # code I posted earlier
  22. $myArr = array();
  23. $myArr[] = "value1";
  24. $myArr[] = "value2";
  25.  
  26. # You can use this like this
  27. echo "First element: ". $myArr[0];
  28. echo "Second element: ". $myArr[1];
  29.  
  30. # And finally you can also given them names
  31. # once you have created the array
  32. $myArr = array();
  33. $myArr["first"] = "value1";
  34. $myArr["second"] = "value2";
  35.  
  36. # You can use this like this
  37. echo "First element: ". $myArr["first"];
  38. echo "Second element: ". $myArr["second"];];
  39.  
Sep 9 '07 #7
Atli
Expert 4TB
Hi Atli
I finally figured out to write the data. However, now I wonder how I can pass these numbers to the page that plots the graph.
I should make it that it passes these values that are around 21 distinct values to be passed to the page that plot the graph.
like:
$data=array(1=>$number1, $number2,......,$number21);
Should use the above code in the same page as the graphing page. I tried it did not work.
Should I call the plotting page and pass these values to that page. I don't know how.
thanks a lot.
You could use sessions to pass the data to your other pages.

Edit:
Couldn't you just query this data form you MySQL server in your graph page?
Sep 9 '07 #8
As you see the code retrieves data from mysql and renames them as $b1, $b2 ... but the graphing part does not / cannot use them. Very strange event!
If I erase the upper part(the bold characters) and do not use the mysql data and just type numbers like: 25, 37, 75 instead of $b1, $b2 and $b3
$data=(1 =>25, 37, 75) it plots the graph very nicely. What causes this hick up?

Here is the graphing code:

Expand|Select|Wrap|Line Numbers
  1. <?php
  2. include('postgraph.class.php'); // this php is in the same folder as this file
  3.  
  4. //connect to database (I connect to database no problem)
  5.  
  6. $tbl_name="mine"; // Table name
  7.  
  8.  
  9. $query = "SELECT x1 FROM $tbl_name"; 
  10. $result = mysql_query($query) 
  11. or die(mysql_error()); 
  12.  
  13.  
  14. # Display results
  15. while($row = mysql_fetch_assoc($result)) {
  16.   $rowNum++;
  17.  
  18. $numbs[] = $row['x1'];
  19.  
  20. }
  21.  
  22. $b1=$numbs[0];
  23.  
  24.  
  25. $b2=$numbs[1];
  26.  
  27. $b3=$numbs[2];
  28. $data = array(1 =>"$b1","$b2","$b3");
  29.  
  30. // creates graph object
  31. $graph = new PostGraph(800,550);
  32.  
  33. // set titles
  34. $graph->setGraphTitles('Math Exam  Sep. 4-2007', '3 Students, Max 75, Min 25');
  35.  
  36. // set format of number on Y axe
  37. $graph->setYNumberFormat('integer');
  38.  
  39. // set number of ticks on Y axe
  40. $graph->setYTicks(10);
  41.  
  42. // set data
  43. $graph->setData($data);
  44.  
  45. $graph->setBackgroundColor(array(237, 237, 237));
  46.  
  47. $graph->setTextColor(array(139,0,0));
  48.  
  49. // set orientation of text on X axe
  50. $graph->setXTextOrientation('horizontal');
  51.  
  52. // prepare image
  53. $graph->drawImage();
  54.  
  55. // print image to the screen
  56. $graph->printImage();
  57. ?>
  58.  
Sep 10 '07 #9
Atli
Expert 4TB
Try passing the $numbs array into the $graph->setData() method. See what that does.

You could also try removing the quote-marks arround the $b1, $b2 and $b3 variables when you create your $data array. The way you are doing it now you are creating character values for you numbers. It shouldn't really mater, PHP is pretty good at figuring stuff like that out for us, but who knows... it might help.

The biggest possible problem I see there though... If your sql query returns less than three rows, or it returns non numerical values, you might not be passing you graph any usable numbers...
Sep 10 '07 #10
I have removed the quotes, I have changed them to single quotes nothing works. My suspicion is that $b1, $b2 are not seen as the numbers that are retrieved from mysql. These numbers when I echo them are 25, ... etc.
As I said when I type the numbers they are plotted but when I use $b1, $b2 they are not.
How can I make sure that they are numerical values that mysql gives me and is seen in the array as numerical values?

Try passing the $numbs array into the $graph->setData() method. See what that does.

You could also try removing the quote-marks arround the $b1, $b2 and $b3 variables when you create your $data array. The way you are doing it now you are creating character values for you numbers. It shouldn't really mater, PHP is pretty good at figuring stuff like that out for us, but who knows... it might help.

The biggest possible problem I see there though... If your sql query returns less than three rows, or it returns non numerical values, you might not be passing you graph any usable numbers...
Sep 10 '07 #11
Hi Atli
I finally checked with gettype( ) and realized that the data I am getting for $b1 and $b2... is string. How can I convert them to numerical or how can I make sure it is numerical instead of string?
Thanks
Sep 10 '07 #12
Hi Atli
I checked mysql_fetch_assoc or array returns string values. However, I need to use mysql_fetch_row to get the numeric values. How can I modify the code to get the desired results? It is not that simple to just change assoc to row. Since when I use the gettype it gives me string type.
Thanks
Sep 10 '07 #13
pbmods
Expert 4TB
Heya, Zion.

mysql_fetch_row() will use numeric keys, but will have no effect on the values.

Note that even though the values are fetched as strings, PHP will automatically convert them to numeric types for mathematical operations.
Sep 10 '07 #14
I know. The problem is that I don't know how to separate these values out from the array outside the while loop. Any suggestions?
Thanks


Heya, Zion.

mysql_fetch_row() will use numeric keys, but will have no effect on the values.

Note that even though the values are fetched as strings, PHP will automatically convert them to numeric types for mathematical operations.
Sep 10 '07 #15

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by jay | last post: by
2 posts views Thread by Uncle_Albert | last post: by
7 posts views Thread by Peter | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.