daniel0816 Posted September 4, 2013 Share Posted 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>"; } ?> Quote Link to comment Share on other sites More sharing options...
Barand Posted September 4, 2013 Share Posted September 4, 2013 Syntax should be WHERE (col1 LIKE '%X%') OR (col2 LIKE '%X%') OR (col3 LIKE '%X%') OR ... etc Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 4, 2013 Author Share Posted 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%')"; Quote Link to comment Share on other sites More sharing options...
Barand Posted September 4, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 4, 2013 Author Share Posted 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? Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 4, 2013 Author Share Posted 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>";} Quote Link to comment Share on other sites More sharing options...
Barand Posted September 4, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 4, 2013 Author Share Posted September 4, 2013 OK is there any way round this? Thanks Again Quote Link to comment Share on other sites More sharing options...
Barand Posted September 4, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 4, 2013 Author Share Posted September 4, 2013 How would that work because each select is taking data from a different table? Quote Link to comment Share on other sites More sharing options...
Barand Posted September 4, 2013 Share Posted September 4, 2013 SELECT A , B, '' as C FROM table1 UNION SELECT '', '', C FROM table2 Quote Link to comment Share on other sites More sharing options...
daniel0816 Posted September 5, 2013 Author Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.