Page 1 of 2

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

Posted: Tue Oct 19, 2004 9:58 am
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.

Posted: Tue Oct 19, 2004 10:05 am
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.

I tried thsi--but it is not working

Posted: Tue Oct 19, 2004 10:27 am
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

Posted: Tue Oct 19, 2004 10:30 am
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>";
}?>

i get the following error

Posted: Tue Oct 19, 2004 10:42 am
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

Posted: Tue Oct 19, 2004 10:58 am
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

Posted: Tue Oct 19, 2004 12:13 pm
by Kingo
Can any one hlep me with the above code

Posted: Tue Oct 19, 2004 1:50 pm
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.

Posted: Tue Oct 19, 2004 2:19 pm
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']}";

Posted: Tue Oct 19, 2004 2:22 pm
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.

Posted: Tue Oct 19, 2004 2:30 pm
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]

Posted: Tue Oct 19, 2004 2:35 pm
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.

Posted: Tue Oct 19, 2004 2:38 pm
by feyd
depending on your table design:

Code: Select all

SELECT * FROM foo WHERE year = 2004 GROUP BY make

Posted: Tue Oct 19, 2004 9:28 pm
by Kingo
Can any one give me a sample code to display the other list box dynamically upon selection from the previous one

Posted: Tue Oct 19, 2004 11:19 pm
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;
}