Link the column of a table in database to list box.

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Link the column of a table in database to list box.

Post by Kingo »

Hello,
I want a list box to generate items from the column of a table in a database. Would appreciate if any one can help me out.
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

Query the database to get the data from the column you want. Loop through the results and output checkboxes for each of the returned rows.
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

I tried thsi--but it is not working

Post by Kingo »

Hello,
I tried the below code just to display the results..But it gets struck and the page is not processed.

Code: Select all

<?php 
echo "<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> ";
echo "</head>   <body> ";
echo "Hello";

require_once('Connections/mysql_conn.php');

$result = MYSQL_QUERY("SELECT year from vehicle")
   or die ("Database not found");

$worked = mysql_fetch_array($result);
echo "<table>";
while($worked)
{
echo "<tr><td>";
echo "$worked";
echo "</tr></td>";
}
echo "</table";
echo "</body></html>";

?>
Any help is really appreciated
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

You almost got it..

Code: Select all

<?php
echo "<table>";
while($worked = mysql_fetch_assoc($result))
{
echo "<tr><td> ". $worked['year'] ." </tr></td>";
}?>
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

i get the following error

Post by Kingo »

I'm getting the following error.

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in c:\inetpub\wwwroot\PHP_RemoteFiles\dyn.php on line 16

And Line 16 is
echo "$worked['year']";


When I changed that one to
echo "$worked[year]"; -- My system gets struck
Plz help
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

I got it worked....
I'm able to display the values from the table.

Code: Select all

<?php 
echo "<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> ";
echo "</head>   <body> ";
echo "Hello";

require_once('Connections/mysql_conn.php');

$result1 = MYSQL_QUERY("SELECT * from vehicle")or die ("Database not found");
while($worked1 = mysql_fetch_array($result1))
{
$M_Make =  $worked1["Make"]; 
$M_Model = $worked1["Model"]; 
$Y_Year =  $worked1["Year"];

echo "Make : $M_Make <br>";
echo "Model :$M_Model<br>";
echo "Year : $Y_Year<br>";
}
   
echo "</body></html>";

?>

I want to put a list box with the year coulmn linked.
i.e; the list box should contain all the years in the table.

Plz help
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Can any one hlep me with the above code
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Code: Select all

<?php 
echo "<html> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> ";
echo "</head>   <body> ";
echo "Hello";

require_once('Connections/mysql_conn.php');

$result1 = MYSQL_QUERY("SELECT * from vehicle")or die ("Database not found");

$list =  "<select name = 'test_list'>";
while($worked1 = mysql_fetch_array($result1))
{
   $Y_Year =  $worked1["Year"];
   $list .= "<option value = '$Y_Year'>Year : $Y_Year</option>";
}
   
echo $list;

echo "</body></html>";

?>
$list will contain a select box labelled "test_list" which will contains options for all the years.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

Post by nigma »

Also, you need to put braces around arrays when they are inside strings.

Code: Select all

// INCORRECT:
echo "$worked['year']";
// CORRECT
echo "{$worked['year']}";
Last edited by nigma on Tue Oct 19, 2004 9:44 pm, edited 4 times in total.
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Thanx....I have begun to solve my problem.
I'm able to display the list.

Slight modifications.....

I'm trying to display only unique records......
I mean to say I have year 2000 many times in the table. But I want it to be shown only once.

I modified my sql query to
Select distinct * from vehicle;

But still it doesnt work.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

with distinct, the entire row you create through your selection has to be unique, for it to only show it once.

maybe you want to look into [mysql_man]group by[/mysql_man]
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Group By works.... Thanx.....

Would really appreciate if you can clarify this to me.
Once I select a year, I should be able to display a list box with MAKE assocaited with that year from the table.

I really thanx you for all ur help.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

depending on your table design:

Code: Select all

SELECT * FROM foo WHERE year = 2004 GROUP BY make
Kingo
Forum Contributor
Posts: 146
Joined: Thu Jun 03, 2004 9:38 am

Post by Kingo »

Can any one give me a sample code to display the other list box dynamically upon selection from the previous one
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

is it really that hard to figure out?

here's some advice:
use at least 2 tables: makes, and models.
If you're feeling fiesty, create tables for engines, parts, linkage between parts and models, linkage between engines and models, etc..

doing these things will reduce the amount of redundancy in your records.

as for a "sample":

Code: Select all

if(isset($_POST['year']) && preg_match('#^\d{4}$#', $_POST['year']))
{
  $year = intval($_POST['year']);
  $sql = "SELECT DISTINCT make_name, make_id FROM models INNER JOIN make ON make_id = model_make WHERE model_year = {$year}";
  $query = mysql_query($sql) or die( mysql_error() );
  if(!$query || mysql_num_rows($query) == 0)
    die( 'fail' );
  $output = '<input type="hidden" name="year" value="' . $year . '"><select name="make">';
  while($row = mysql_fetch_assoc($query)) $output .= '<option value="' . $row['make_id'] . '">' . $row['make_name'] . '</option>';
  $output .= '</select>';
  echo $output;
}
Post Reply