PHP retrieve the values of only one column

[zionlion]

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.

[Atli]

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:

SELECT column1, column2 FROM myTable;
 

So to do this in PHP:

# Connect
$db = mysql_connect("host", "user", "passwd") or die(mysql_error());
mysql_select_db("mydb", $db) or die(mysql_error());
 
# Execute query
$sql = "SELECT col1 FROM myTbl";
$result = mysql_query($sql, $db) or die(mysql_error());
 
# Display results
while($row = mysql_fetch_assoc($result)) {
  $rowNum++;
  echo "Row $rowNum: ". $row['col1'];
}
 
# Free resources
mysql_free_result($result);
mysql_close($db);
 

[zionlion]

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.

[Atli]

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:

# To give each ID a numeric index
$data[] = $row['myID'];
 
# To give each ID an associative index
$data["ID_number_$rowNum"] = $row['myID'];
 

[zionlion]

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:

# To give each ID a numeric index
$data[] = $row['myID'];
 
# To give each ID an associative index
$data["ID_number_$rowNum"] = $row['myID'];
 

[zionlion]

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.

[Atli]

Arrays are easy to use.

You can create the in several ways. For example:

# You can enter the data into it as you create it
# in which case each element will be given a numeric index
# starting from 0.
$myArr = array("value1", "value2", "value3");
 
# You can use this like this
echo "First element: ". $myArr[0];
echo "Second element: ". $myArr[1];
 
 
# You can also give the elements names instead
# of the numeric indexes.
$myArr = array("first" => "value1", "second" => "value2");
 
# Which you can use like this
echo "First element: ". $myArr["first"];
echo "Second element: ". $myArr["second"];
 
# It is also possible to add the data after
# the array has been created, like I did in the
# code I posted earlier
$myArr = array();
$myArr[] = "value1";
$myArr[] = "value2";
 
# You can use this like this
echo "First element: ". $myArr[0];
echo "Second element: ". $myArr[1];
 
# And finally you can also given them names
# once you have created the array
$myArr = array();
$myArr["first"] = "value1";
$myArr["second"] = "value2";
 
# You can use this like this
echo "First element: ". $myArr["first"];
echo "Second element: ". $myArr["second"];];
 

[Atli]

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?

[zionlion]

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:

<?php
include('postgraph.class.php'); // this php is in the same folder as this file
 
//connect to database (I connect to database no problem)
 
$tbl_name="mine"; // Table name
 
 
$query = "SELECT x1 FROM $tbl_name"; 
$result = mysql_query($query) 
or die(mysql_error()); 
 
 
# Display results
while($row = mysql_fetch_assoc($result)) {
  $rowNum++;
 
$numbs[] = $row['x1'];
 
}
 
$b1=$numbs[0];
 
 
$b2=$numbs[1];
 
$b3=$numbs[2];
$data = array(1 =>"$b1","$b2","$b3");
 
// creates graph object
$graph = new PostGraph(800,550);
 
// set titles
$graph->setGraphTitles('Math Exam  Sep. 4-2007', '3 Students, Max 75, Min 25');
 
// set format of number on Y axe
$graph->setYNumberFormat('integer');
 
// set number of ticks on Y axe
$graph->setYTicks(10);
 
// set data
$graph->setData($data);
 
$graph->setBackgroundColor(array(237, 237, 237));
 
$graph->setTextColor(array(139,0,0));
 
// set orientation of text on X axe
$graph->setXTextOrientation('horizontal');
 
// prepare image
$graph->drawImage();
 
// print image to the screen
$graph->printImage();
?>
 

[Atli]

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...

[zionlion]

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...

[zionlion]

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

[zionlion]

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

[pbmods]

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.

[zionlion]

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.