Jump to content

query full database


daniel0816

Recommended Posts

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>";
}
	

?>
Link to comment
Share on other sites

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%')";
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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>";}
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.