Ambiguous column query help

[Obsession]

$sql="SELECT * FROM cars,drivers WHERE $SearchIn='$SearchValue'";
$result=mysql_query($sql) or die(mysql_error());

 

Now, you see I am selecting values where $SearchIn=$SearchValue.

 

$SearchIn can be any one of these -

 

						<option value="" selected="yes">Choose One...</option>
					<option value=""></option>
					<option value="CarNum">Car Number</option>
					<option value="CarColor">Car Color</option>
					<option value="CarMake">Car Make</option>
					<option value="CarType">Car Type</option>
					<option value="CarReg">Car Registration</option>
					<option value=""></option>
					<option value="DriverName">Driver Name</option>
					<option value="DriverAddress">Driver Address</option>
					<option value="DriverHomeNum">Driver Home Phone</option>
					<option value="DriverMobileNum">Driver Mobile Phone</option>

 

Although, we assume the user will pick one of the options other than empty or Please Choose One.

Now, if the choice is one of the ones starting with "Driver"(the bottom four), I need the query to search for whatever was input in the Drivers table. If it was one of the top five, I need it to search in the Cars table. BUT, the results I want to display are from both tables.

 

An example, car 11. If we search for "Peter", driver 11's name, I need it to search the driver table for this, then print all the results from the driver table, THEN using the result in the CarNum field in the driver table, print everything from the Cars table.  But, if I search for "Ford" I need it to search the cars table, then search the drivers and print it all. How do I do this?

 

You see, CarNum is in both tables.

 

Now, my problems.

 

If I search for CarNum, value of 11, which should return one result, I get "Column 'CarNum' in where clause is ambiguous". No idea how to solve it, never ever seen it before.

 

 

 

Now, if i search for "Peter" under DriverName, it should return the following-

 

Car Number	Car Color	Car Type	Car Make	Car Reg	Driver Name	Driver Address	Phone Number	Mobile Number
11	Silver	6 Seater	Citroen	9999888	Peter	6 Brig St	01229	07792

 

But, it returns this-

 

Car Number	Car Color	Car Type	Car Make	Car Reg	Driver Name	Driver Address	Phone Number	Mobile Number
11	Silver	6 Seater	Citroen	9999888	Peter	6 Brig St	01229	07792
56	Red	Saloon	Ford	1111333	Tim	3 Jarrow St	01229	07792

 

As you can see, "Tim" is not "Peter". Why does it return that?

 

[roopurt18]

You fix ambiguous column errors by assigning an alias to each table in the query and then refer to the columns by: table_alias.column_name.

 

For example:

 

select a.*, b.*

from table1 a

inner join table2 b on a.id=b.table1_fk

where a.column1='somevalue' and b.column1='someothervalue';

 

Take note of how I've used a and b to identify the tables throughout the query.

[Obsession]

Sorted it now, but thanks anyway. My last problem is that when they search a value and select one of the driver options, i need a way of getting the carnumber of ALL drivers returned from the search, then with those car numbers print the table in this order-

 

Car Number   Car Color   Car Type   Car Make   Car Reg   Driver Name   Driver Address   Phone Number   Mobile Number

 

Been messing around with while loops, but I just cant do it. I hope it worded it well enough.

[roopurt18]

How about showing us the create table statements for each of those tables?

[Obsession]

mysql_query("CREATE TABLE Drivers
(
DriverName TEXT,
DriverAddress TEXT,
DriverHomeNum INT(11),
DriverMobileNum INT(11),
Carnum INT(3)
)");

mysql_query("CREATE TABLE Cars
(
CarNum TEXT,
CarColor TEXT,
CarType INT(11),
CarMake INT(11),
CarReg TEXT
)");

 

Creates both tables.

 

Now, when they search for one of the options that searches the car table, this code works.

 

if (($SearchIn=="CarNum")||($SearchIn=="CarColor")||($SearchIn=="CarMake")||($SearchIn=="CarType")||($SearchIn=="CarReg"))
				{
					$sql="SELECT * FROM cars WHERE $SearchIn='$SearchValue'";
					$result=mysql_query($sql) or die(mysql_error());

							echo "<table border=1>";
							echo "<tr>";
							echo "<th>Car Number</th>";
							echo "<th>Car Color</th>";
							echo "<th>Car Type</th>";
							echo "<th>Car Make</th>";
							echo "<th>Car Reg</th>";
							echo "<th>Driver Name</th>";
							echo "<th>Driver Address</th>";
							echo "<th>Phone Number</th>";
							echo "<th>Mobile Number</th>";
							echo "</tr>";

						while ($row=mysql_fetch_array($result))
						{

							echo "<tr>";
							echo "<td>"; echo $row['CarNum']; echo "</td>";
							echo "<td>"; echo $row['CarColor']; echo "</td>";
							echo "<td>"; echo $row['CarType']; echo "</td>";
							echo "<td>"; echo $row['CarMake']; echo "</td>";
							echo "<td>"; echo $row['CarReg']; echo "</td>";

								$DriverNumber=$row['CarNum'];
								$sql="SELECT * FROM drivers WHERE Carnum=$DriverNumber";
								$result2=mysql_query($sql);

								while ($row=mysql_fetch_array($result2))
								{
								echo "<td>"; echo $row['DriverName']; echo "</td>";
								echo "<td>"; echo $row['DriverAddress']; echo "</td>";
								echo "<td>"; echo $row['DriverHomeNum']; echo "</td>";
								echo "<td>"; echo $row['DriverMobileNum']; echo "</td>";
								echo "</tr>";
								}

						}
					echo "</table>";
				}
			else

 

But, they may also search the driver table for driver information, so the query is then

 

else
				{
					$sql="SELECT * FROM drivers WHERE $SearchIn='$SearchValue'";
					$result=mysql_query($sql) or die(mysql_error());

							echo "<table border=1>";
							echo "<tr>";
							echo "<th>Car Number</th>";
							echo "<th>Car Color</th>";
							echo "<th>Car Type</th>";
							echo "<th>Car Make</th>";
							echo "<th>Car Reg</th>";
							echo "<th>Driver Name</th>";
							echo "<th>Driver Address</th>";
							echo "<th>Phone Number</th>";
							echo "<th>Mobile Number</th>";
							echo "</tr>";

 

But now, I am completely lost. You see,if i search DriverName for "Peter", i get two results. If I do a while loop on the $result['Carnum'] variable...i get all the car numbers that have drivers named Peter, yes? Then how do i use those car numbers to do what the first part does and print all this data?  (notice the small 'n' in num, and see how this matches the drivers table whereas the cars table has a big 'N'. Does that matter?)

[roopurt18]

TEXT fields are used for storing very large text fields.  You should not be using TEXT for items like DriverName or DriverAddress.

 

CarNum exists in both tables, so I assume that you want to use it as a key field.  However it is declared as INT in one table and TEXT in the other.  It should probably be INT in both.

 

We need to clean up your table structure before we waste any time writing code to work with them.

[Obsession]

My bad, I generally just stick it all as text, bad habit.

 

Anyway, new file-

 

<?php
$con=mysql_connect('localhost','root','');
if(!$con)
{
echo mysql_error();
}
$select=mysql_select_db('a1',$con);
if(!$select)
{
echo mysql_error();
}

mysql_select_db('a1',$con);

mysql_query("CREATE TABLE Drivers
(
DriverName VARCHAR(22),
DriverAddress TEXT,
DriverHomeNum INT(11),
DriverMobileNum INT(11),
CarNum INT(3)
)");

mysql_query("CREATE TABLE Cars
(
CarNum Int(3),
CarColor VARCHAR(22),
CarType INT(11),
CarMake INT(11),
CarReg VARCHAR(22)
)");

header('Location:index.html');
?>

 

Next step? Is there not some way I can identify CarNum as primary key?

[roopurt18]

Search for PRIMARY KEY within the document to see how it's used.  You only create one PRIMARY KEY `CarNum` column and it goes in the `Cars` table.