473,394 Members | 1,289 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,394 software developers and data experts.

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
14 6153
Atli
5,058 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
5,058 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
5,058 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
5,058 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
5,058 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
5,821 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

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

Similar topics

5
by: nicholas | last post by:
I would like to insert a new record in a Ms Access database and retrieve the newly created ID (autonumber). I would like to do it in VB.NET for an ASP.NET page. ( no asp) I found several...
4
by: jay | last post by:
I am using the dataset object to add a row to a sql server database in vb.net code, as follows: dim drow as DataRow dim cmdBld as new SqlCommandBuilder(mySqlDataAdapter) ds.tables(0).NewRow()...
2
by: Uncle_Albert | last post by:
Hi peeps, Here's a screenshot of a particular form in my program. http://www.jdr.dsl.pipex.com/dg1.JPG Basically, when I click on 'Refresh Totals', I want it to retrieve all values from the...
7
by: Peter | last post by:
Gday, I have a dataset with multiple tables, from which I want to access a single value in one of those tables. I know I can do something like: Decimal myVar =...
0
by: padhuwork | last post by:
Hi, I want to create a Windows DLL using VC++ 6.0 which connects to SQL Server. After establishing the connection, I want to retrieve records from table (query) and out put the recordset to a...
9
by: Mel | last post by:
I have 10 columns total. 3 of them are invisible. The rest are read- only BoundFields, 3 of which are editable fields using TemplateFields. Upon editing, I want to validate what the user enters...
1
by: san1014 | last post by:
Hi I have a table SQL> select * from nodes; NODE_ID NODE_NAME -------------------- ------------------------------ N1 Kothhapet N2 Nagole...
4
by: TechnoAtif | last post by:
Hi ALL I have entered some array values using checkboxes into mysql database through a form. Next iam creating a searchpage where all those cateogories inserted through checkboxes has to be...
4
by: questionit | last post by:
How do i retreive 10 rows from a table using SQL SELECT Names from Customers... how do i change it to retrieve only 'n' number of rows? Please suggest an easy method Thanks
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.