query full database

daniel0816 · September 4, 2013

I am trying to query a database with multiple tables and echo each piece of data from each table into an html table. I get the error that says the syntax for the query is incorrect. Any suggestions would be greatly appreciated thanks.

<?php
error_reporting(E_ALL);
$connect = mysql_connect("dbinfo", "dbinfo", "dbinfo");
//select database
$dbName= "dbinfo";
mysql_select_db($dbName, $connect);

$sql='SELECT CUST_ID, CUST_Forename, CUST_Surname, CUST_Email, CUST_Mobile, CUST_HomeNum,
CUST_AddressL1, CUST_AddressL2, CUST_AddressL3, CUST_Postcode, J_RefNum, MANU_Name, J_Model, OS_Name, J_ReceivedBy, J_DateRec, J_FaultDesc,
J_PassWinAdmin, J_DataRecYN, J_PoweSuppYN, JS_Status	
FROM $dbName
WHERE CUST_ID, CUST_Forename, CUST_Surname, CUST_Email, CUST_Mobile, CUST_HomeNum,
CUST_AddressL1, CUST_AddressL2, CUST_AddressL3, CUST_Postcode, J_RefNum, MANU_Name, J_Model, OS_Name, J_ReceivedBy, J_DateRec, J_FaultDesc,
J_PassWinAdmin, J_DataRecYN, J_PoweSuppYN, JS_Status
LIKE "%'. addslashes($_POST['sCriteria']) .'%" 
';
	
$result=mysql_query($sql) or die($sql."<br/><br/>".mysql_error());
	
echo "<table border='1' cellpadding='2'>";
echo "<tr>
		<th>Customer ID</th>
		<th>Forename</th>
		<th>Surname</th>
		<th>Email</th>
		<th>Mobile Number</th> 
		<th>Home Number</th>
		<th>Address Line 1</th>
		<th>Address Line 2</th>
		<th>Address Line 3</th>
		<th>Postcode</th>
		<th>Job Reference Number</th>
		<th>Manufacturer</th>
		<th>Model</th>
		<th>Operating System</th>
		<th>Received By</th>
		<th>Date Received</th>
		<th>Fault Description</th>
		<th>Password - Windows Admin</th>
		<th>Data Recovery?</th>
		<th>Power Supply?</th>
		<th>Job Status</th>
	 </tr>";

	
while($row=mysql_fetch_array($result))
{
	echo "<tr>";
	
		echo '<td>' . $row['CUST_ID'] . '</td>';
		echo '<td>' . $row['CUST_Forename'] . '</td>';
		echo '<td>' . $row['CUST_Surname'] . '</td>';
		echo '<td>' . $row['CUST_Email'] . '</td>';
		echo '<td>' . $row['CUST_Mobile'] . '</td>';;
		echo '<td>' . $row['CUST_HomeNum'] . '</td>';
		echo '<td>' . $row['CUST_AddressL1'] . '</td>';
		echo '<td>' . $row['CUST_AddressL2'] . '</td>';
		echo '<td>' . $row['CUST_AddressL3'] . '</td>';
		echo '<td>' . $row['CUST_Postcode'] . '</td>';
		echo '<td>' . $row['J_RefNum'] . '</td>';
		echo '<td>' . $row['MANU_Name'] . '</td>';
		echo '<td>' . $row['J_Model'] . '</td>';
		echo '<td>' . $row['OS_Name'] . '</td>';
		echo '<td>' . $row['J_ReceivedBy'] . '</td>';
		echo '<td>' . $row['J_DateRec'] . '</td>';
		echo '<td>' . $row['J_FaultDesc'] . '</td>';
		echo '<td>' . $row['J_PassWinAdmin'] . '</td>';
		echo '<td>' . $row['J_DataRecYN'] . '</td>';
		echo '<td>' . $row['J_PowerSuppYN'] . '</td>';
		echo '<td>' . $row['JS_Status'] . '</td>';
		
	echo "</tr>";
	echo "</table>";
}
	

?>

Barand · September 4, 2013

Syntax should be

WHERE (col1 LIKE '%X%') OR (col2 LIKE '%X%') OR (col3 LIKE '%X%') OR ... etc

daniel0816 · September 4, 2013

Can you tell me what is wrong with the syntax in the following query. Any help would be greatly appreciated thanks.

$sql="SELECT CUST_ID, CUST_Forename, CUST_Surname, CUST_Email, CUST_Mobile, 
CUST_HomeNum, CUST_AddressL1, CUST_AddressL2, CUST_AddressL3, 
CUST_Postcode FROM Customers WHERE (CUST_ID LIKE '%$criteria%') OR 
(CUST_Forename LIKE '%$criteria%') OR (CUST_Surname LIKE '%$criteria%') OR 
(CUST_Email LIKE '%$criteria%') OR (CUST_Mobile LIKE '%$criteria%') OR 
(CUST_HomeNum LIKE '%$criteria%') OR (CUST_AddressL1 LIKE 
'%$criteria%') OR (CUST_AddressL2 LIKE '%$criteria%') OR (CUST_AddressL3 LIKE 
'%$criteria%') OR (CUST_Postcode LIKE 
'%$criteria%') UNION SELECT J_RefNum, J_Model, J_ReceivedBy, 
J_DateRec, J_FaultDesc, J_PassWinAdmin, J_DataRecYN, J_PoweSuppYN, FROM Jobs 
WHERE (J_RefNum LIKE '%$criteria%') OR (J_Model LIKE '%$criteria%') OR 
(J_ReceivedBy LIKE '%$criteria%') OR (J_DateRec LIKE '%$criteria%') OR 
(J_FaultDesc LIKE '%$criteria%') OR (J_PassWinAdmin LIKE '%$criteria%') OR 
(J_DataRecYN LIKE '%$criteria%') OR (J_PoweSuppYN LIKE 
'%$criteria%') UNION SELECT MANU_Name FROM Manufacturers WHERE 
(MANU_Name LIKE '%$criteria%') UNION SELECT OS_Name FROM 
OperatingSystemm WHERE (OS_Name LIKE 
'%$criteria%') UNION SELECT JS_Status FROM JobStatus WHERE 
(JS_Status LIKE '%$criteria%')";

Barand · September 4, 2013

A union of two or more queries returns a continuous result set from those queries. Therefore the individual queries must retrieve the same number of columns and corresponding columns must be of the same type.

WRONG

SELECT A, B FROM table1
UNION
SELECT C FROM table2

OK

SELECT A, B FROM table1
UNION
SELECT C, null FROM table2

daniel0816 · September 4, 2013

Ok but will I need to change anything with the where clauses i.e. have the same amount so have them set to null?

daniel0816 · September 4, 2013

OK ive changed it around and there are no faults with the syntax however after it executes the first select it doesn't do the second, third or fourth. It just says undefined index but this error is not making reference to the columns specified. Its actually referring to the code I use to submit the results of the query into the HTML table, but yet it still submits the results from the first select. Any help would be greatly appreciated thanks.

Here is my code:

This my query:

$sql="SELECT CUST_ID, CUST_Forename, CUST_Surname, CUST_Email, CUST_Mobile, 
CUST_HomeNum, CUST_AddressL1, CUST_AddressL2, CUST_AddressL3, 
CUST_Postcode FROM Customers WHERE (CUST_ID LIKE '%$criteria%') OR 
(CUST_Forename LIKE '%$criteria%') OR (CUST_Surname LIKE '%$criteria%') 
OR (CUST_Email LIKE '%$criteria%') OR (CUST_Mobile LIKE '%$criteria%') 
OR (CUST_HomeNum LIKE '%$criteria%') OR (CUST_AddressL1 LIKE 
'%$criteria%') OR (CUST_AddressL2 LIKE '%$criteria%') 
OR (CUST_AddressL3 LIKE '%$criteria%') OR (CUST_Postcode LIKE 
'%$criteria%') UNION SELECT J_RefNum, J_Model, J_ReceivedBy, 
J_DateRec, J_FaultDesc, J_PassWinAdmin, J_DataRecYN, J_PowerSuppYN, '', 
'' FROM Jobs WHERE (J_RefNum LIKE '%$criteria%') OR (J_Model LIKE 
'%$criteria%') OR (J_ReceivedBy LIKE '%$criteria%') OR (J_DateRec LIKE 
'%$criteria%') OR (J_FaultDesc LIKE '%$criteria%') OR (J_PassWinAdmin 
LIKE '%$criteria%') OR (J_DataRecYN LIKE '%$criteria%') OR 
(J_PowerSuppYN LIKE '%$criteria%') UNION SELECT MANU_Name, '', 
'', '', '', '', '', '', '', '' FROM Manufacturers WHERE (MANU_Name LIKE 
'%$criteria%') UNION SELECT OS_Name, '', '', '', '', '', '', 
'', '', '' FROM OperatingSystems WHERE (OS_Name LIKE 
'%$criteria%') UNION SELECT JS_Status,'', '', '', '', '', '', 
'', '', '' FROM JobStatus  WHERE (JS_Status LIKE 
'%$criteria%')";

The code below is to submit the results of the query to the HTML table:

while($row=mysql_fetch_array($result)){ echo 
"<tr>";   echo '<td>' . $row['CUST_ID'] . 
'</td>';  echo '<td>' . $row['CUST_Forename'] . 
'</td>';  echo '<td>' . $row['CUST_Surname'] . 
'</td>';  echo '<td>' . $row['CUST_Email'] . 
'</td>';  echo '<td>' . $row['CUST_Mobile'] . 
'</td>';;  echo '<td>' . $row['CUST_HomeNum'] . 
'</td>';  echo '<td>' . $row['CUST_AddressL1'] . 
'</td>';  echo '<td>' . $row['CUST_AddressL2'] . 
'</td>';  echo '<td>' . $row['CUST_AddressL3'] . 
'</td>';  echo '<td>' . $row['CUST_Postcode'] . 
'</td>';  echo '<td>' . $row['J_RefNum'] . 
'</td>';  echo '<td>' . $row['MANU_Name'] . 
'</td>';  echo '<td>' . $row['J_Model'] . 
'</td>';  echo '<td>' . $row['OS_Name'] . 
'</td>';  echo '<td>' . $row['J_ReceivedBy'] . 
'</td>';  echo '<td>' . $row['J_DateRec'] . 
'</td>';  echo '<td>' . $row['J_FaultDesc'] . 
'</td>';  echo '<td>' . $row['J_PassWinAdmin'] . 
'</td>';  echo '<td>' . $row['J_DataRecYN'] . 
'</td>';  echo '<td>' . $row['J_PowerSuppYN'] . 
'</td>';  echo '<td>' . $row['JS_Status'] . 
'</td>';   echo "</tr>"; echo 
"</table>";}

Barand · September 4, 2013

As I said, you have one continuous result set. The ten column names in the result set are not suddenly going to change part way through the results.

So, for example, as MANU_name is in the first column selected in that SELECT clause it will be in $row['CUST_ID'] as that will be the name of the first column in the results. There will be no $row['MANU_name'] and that goes for others too.

daniel0816 · September 4, 2013

OK is there any way round this?

Thanks

Again

Barand · September 4, 2013

You could select 21 fields in each select statement, defining their names with column aliases in the first select and aligning them with the blank fields in the others

daniel0816 · September 4, 2013

How would that work because each select is taking data from a different table?

Barand · September 4, 2013



SELECT A , B, '' as C FROM table1

UNION

SELECT '', '', C FROM table2

daniel0816 · September 5, 2013

Thanks the query works fine but when I echo the results into the html table the data isn't lined up correctly due to the layout of the query. Is there any way to hide the empty cells so that everything is lined up correctly.

Thanks

Again

Sign In

query full database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information