Drop-down fetch data

[neodize]

Hi

Ive been figuring this out for hours and still dont get it

<?php 
	$q2 = "SELECT * FROM issueveh WHERE plateno='$plateno'";
	$r2 = mysql_query($q2) or die ("Error reading from database");
	while($rows2=mysql_fetch_array($r2)) {
	if ($plateno == $rows2['plateno']) {
	echo "<option selected value=\"". $rows2['vehicle'] . "\">" . $rows2['vehicle'] . "</option>";
} else {
	$q3 = "SELECT * FROM vehicle";
	$r3 = mysql_query($q3) or die ("Error reading from database");
	while($rows2=mysql_fetch_array($r3)) {
	echo "<option value=\"". $rows2['vehicle'] . "\">" . $rows2['vehicle'] . "</option>";

}
}
}
?>

What im trying to do is when is to fetch data from issueveh using $plateno and put selected on the one queried. and list down the rest of the list from another table

and if it doesnt exist i can add record

HELP! im so lost 😕

[niroshan]

Hi neodize,

The whole problem with this question is lies on the database design. How do you designed the database?Why did you repeat the vehicle field in both tables. Remember normalization is more important when u design detabases.

Assume you have below table structure,

issueveh

id - primary key
vehicle_id - foreign key for vehicle table
plate

vehicle

id - primary key
vehicle

if this was the structure, you can have a PHP code as,

$vehicleId = '';
//never use * om select queries. get only the field you want
$q2 = "SELECT vehicle_id FROM issueveh WHERE plateno='$plateno'";
$r2 = mysql_query($q2) or die ("Error reading from database");
if(mysql_num_rows($r2) > 0)
{
    $rows2=mysql_fetch_array($r2);
    $vehicleId = $rows2['vehicle_id'];
}

$q3 = "SELECT vehicle, id FROM vehicle";
$r3 = mysql_query($q3) or die ("Error reading from database");
while($rows2=mysql_fetch_array($r3)) {
    if($rows2['id'] == $vehicleId)
        $selected = ' selected';
    else
        $selected = '';
    echo "<option value=\"". $rows2['vehicle'] . "\"".$selected .">" . $rows2['vehicle'] . "</option>";
}

Hope this is clear.

I`m not sure about your requirement but according to what i understood from these two tables we can put both the tables in to one table,

vehicle

id - primary key
vehicle
plate
vehicle_issued (int 1) (1 or 0)

Thanks,
Best regards,
Niroshan

[neodize]

Thanks!

I finally figured out with this:

$q1 = "SELECT * FROM issueveh WHERE id='$id'";
$r1 = mysql_query($q1) or die (mysql_error());
while($rows = mysql_fetch_array($r1)) {
echo "<option selected value=\"". $rows['category'] . "\">" . $rows['category'] . "</option>";

$cat = $rows['category'];

$q2 = "SELECT * FROM vehicle WHERE category NOT IN ('$cat')";
$r2 = mysql_query($q2) or die ("Error reading from database");
while($rw2=mysql_fetch_array($r2)) 
echo "<option value=\"". $rw2['category'] . "\">" . $rw2['category'] . "</option>";

The thing is i wanted to list down the category that's not in $cat from a diff table!