daniel0816

Yea I never thought about it that way I apologize. The reason why I am saying I dont want to use union is because I had already tried it and the query worked great but it didnt output the data correctly into the HTML table. Each set of data wasnt lined up correctly and I dont know how to remove the empty table cells inorder to align the data up correctly in the table

Well thanks alot

How is that relevant for this all I want to know is how to combine these two simple queries together and thats it. The relationships I have between my tables is not of your concern all I ask for is a simple solution to a small problem i.e. combine the two queries above. The result that I expect should be self explanatory through the two simple queries above I just want to know how to join them together into one query. Yes you are probably right I shouldnt be creating multiple threads but I really need to find a solution to this problem asap.

I have the two following queries: SELECT CUST_ID FROM Customers WHERE CUST_ID LIKE '%$criteria%' SELECT J_RefNum FROM Jobs WHERE J_RefNum LIKE '%$criteria%' How would I merge them together into one query without using union. Any help would be greatly appreciated thanks.

Ok apologies here is my query to go along with it. By the way there are only two sets of data that are stored in the database, really dont know why it repeats them. Thanks again. $criteria = $_POST['sCriteria']; $sql="SELECT Customers.CUST_ID, Customers.CUST_Forename, Customers.CUST_Surname, Customers.CUST_Email, Customers.CUST_Mobile, Customers.CUST_HomeNum, Customers.CUST_AddressL1, Customers.CUST_AddressL2, Customers.CUST_AddressL3, Customers.CUST_Postcode, Jobs.J_RefNum, Manufacturers.MANU_Name, Jobs.J_Model, OperatingSystems.OS_Name, Jobs.J_ReceivedBy, Jobs.J_DateRec, Jobs.J_FaultDesc, Jobs.J_PassWinAdmin, Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, JobStatus.JS_Status FROM Customers, Jobs, Manufacturers, OperatingSystems, JobStatus WHERE ( Customers.CUST_ID LIKE '%$criteria%') OR ( Customers.CUST_Forename LIKE '%$criteria%') OR (Customers.CUST_Surname LIKE '%$criteria%') OR (Customers.CUST_Email LIKE '%$criteria%') OR (Customers.CUST_Mobile LIKE '%$criteria%') OR (Customers.CUST_HomeNum LIKE '%$criteria%') OR (Customers.CUST_AddressL1 LIKE '%$criteria%') OR (Customers.CUST_AddressL2 LIKE '%$criteria%') OR (Customers.CUST_AddressL3 LIKE '%$criteria%') OR (Customers.CUST_Postcode LIKE '%$criteria%') OR (Jobs.J_RefNum LIKE '%$criteria%') OR (Manufacturers.MANU_Name LIKE '%$criteria%') OR (Jobs.J_Model LIKE '%$criteria%') OR (OperatingSystems.OS_Name LIKE '%$criteria%') OR (Jobs.J_ReceivedBy LIKE '%$criteria%') OR (Jobs.J_DateRec LIKE '%$criteria%') OR (Jobs.J_FaultDesc LIKE '%$criteria%') OR (Jobs.J_PassWinAdmin LIKE '%$criteria%') OR (Jobs.J_DataRecYN LIKE '%$criteria%') OR (Jobs.J_PowerSuppYN LIKE '%$criteria%') ";

Well if I didnt know what data it was returning do you think I would have posted with the problem of repeating the data over and over?

I have a MySQL query which gets the required data ok. But when I submit the results into the HTML table it displays the data ok but it repeats it over and over. Here is my code: $result=mysql_query($sql) or die(mysql_error()); if($result) { ?> <table name='details' border='2'> <thead> <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> </thead> <tbody> <?php } 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>"; } ?> </tbody> </table> Thanks Again

I want to remove individual empty cells.

Ok bad example for talk sake assume that the customer table has a MANU_ID.

Say I have the following queries: "SELECT CUST_ID, CUST_Forename, CUST_Surname FROM Customers WHERE (CUST_ID LIKE '%$criteria%') OR (CUST_Forename LIKE '%$criteria%') OR (CUST_Surname LIKE '%$criteria%')"; "SELECT MANU_Name FROM Manufacturers WHERE (MANU_Name LIKE '%$criteria%')"; What I want to do is have these two queries in one query. I also want the query to echo the results into an HTML table like the following: <table name='details' border='2'> <thead> <tr> <th>Customer ID</th> <th>Forename</th> <th>Surname</th> <th>Manufacturer</th> </tr> </thead> <tbody> echo "<tr> <td>{$row['CUST_ID']}</td> <td>{$row['CUST_Forename']}</td> <td>{$row['CUST_Surname']}</td> <td>{$row['MANU_Name']}</td> </tr>\n"; Thanks Again

Can anyone provide me with an alternative query that does the same job as the following solution. Using UNION causes the result to not be suitable for what I want to do with it. 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, '' as J_RefNum, '' as MANU_Name, '' as J_Model, '' as OS_Name, '' as J_ReceivedBy, '' as J_DateRec, '' as J_FaultDesc, '' as J_PassWinAdmin, '' as J_DataRecYN, '' as J_PowerSuppYN, '' as JS_Status 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%')";

yea that doesn't work either. What I am trying to do is remove the empty cells not hide them.

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

How do I set empty table cells to hidden so that they don't show up on screen therefore all data in the table will be aligned correctly. Here is what I have tried so far: <tbody style="empty-cells:hidden"><?php} while($row=mysql_fetch_assoc($result)){ echo "<tr> <td>{$row['CUST_ID']}</td> <td>{$row['CUST_Forename']}</td> <td>{$row['CUST_Surname']}</td> <td>{$row['CUST_Email']}</td> <td>{$row['CUST_Mobile']}</td> <td>{$row['CUST_HomeNum']}</td> <td>{$row['CUST_AddressL1']}</td> <td>{$row['CUST_AddressL2']}</td> <td>{$row['CUST_AddressL3']}</td> <td>{$row['CUST_Postcode']}</td> <td>{$row['J_RefNum']}</td> <td>{$row['MANU_Name']}</td> <td>{$row['J_Model']}</td> <td>{$row['OS_Name']}</td> <td>{$row['J_ReceivedBy']}</td> <td>{$row['J_DateRec']}</td> <td>{$row['J_FaultDesc']}</td> <td>{$row['J_PassWinAdmin']}</td> <td>{$row['J_DataRecYN']}</td> <td>{$row['J_PowerSuppYN']}</td> <td>{$row['JS_Status']}</td> </tr>\n"; } Any suggestions would be greatly appreciated thanks

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

OK is there any way round this? Thanks Again

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

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

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%')";

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

I am trying to get the update button in the html table to call up a pop up box with a drop down list in it with an exit button that closes the pop up box. Below is the code I have so far: <html> <body> <?php error_reporting(E_ALL); $connect = mysql_connect("dbinfo", "dbinfo", "dbinfo"); //select database mysql_select_db("dbinfo", $connect); $select = "SELECT * FROM JobStatus"; $result = mysql_query($select); if($result) { ?> <table border="2"> <thead> <tr> <th>Job Status ID</th> <th>Job Reference Number</th> <th>Job Status</th> <th>Update?</th> </tr> </thead> <tbody> <?php } if(mysql_num_rows($result)==0) { echo '<tr><td colspan="4">No Rows Returned</td></tr>'; } else { while($row = mysql_fetch_assoc($result)) { echo "<tr> <td>{$row['JS_ID']}</td> <td>{$row['J_RefNum']}</td> <td>{$row['JS_Status']}</td> <td><button name=update class=updatebtn>Update Status</button></td> </tr>\n"; } ?> </tbody> </table> <?php } ?> </body> </html> Any help would be greatly appreciated thanks

I am trying to retrieve all the data from the Job Status table and display it in an html table. This is the code I have, I get the following error: Parse error: syntax error, unexpected end of file <html> <body> <?php error_reporting(E_ALL); $connect = mysql_connect("dbinfo", "dbinfo", "dbinfo"); //select database mysql_select_db("dbinfo", $connect); $select = "SELECT * FROM JobStatus"; $result = mysql_query($select); if($result) { ?> <table border="2"> <thead> <tr> <th>Job Status ID</th> <th>Job Reference Number</th> <th>Job Status</th> </tr> </thead> <tbody> <?php if(mysql_num_rows($result)==0) { echo '<tr><td colspan="4">No Rows Returned</td></tr>'; } else { while($row = mysql_fetch_assoc($result)) { echo "<tr> <td>{$row['JS_ID']}</td> <td>{$row['J_RefNum']}</td> <td>{$row['JS_Status']}</td> </tr>\n"; } ?> </tbody> </table> <?php }; ?> </body> </html> Thanks again

I am trying to write a query that executes when the user types in a number into a textbox and then clicks the search button. The query is supposed to get the JS_Status from the JobStatus table with the JS_ID that matches the value that has been submitted through the textbox and display it in an alert box. The code I have runs but it gives me this weird message in the alert box saying Resource id#3 which I am hoping someone could tell me what that is. Here is my code: <?php error_reporting(E_ALL); $connect = mysql_connect("dbinfo", "dbinfo", "dbinfo"); //select database mysql_select_db("dbinfo", $connect); $refNum = $_POST["refNum"]; $status = mysql_query("SELECT JS_Status FROM JobStatus WHERE $refNum = JS_ID"); if($status) { echo '<script language="javascript" type="text/javascript"> alert(\''.$status .'\'); </script>'; }else { die ("could not run query"); } ?> Thanks again

dont worry about it brotha cus i got it thank god thanks for the help really apppreciate it

Well all I am doing is trying to insert the value of the auto incremented J_RefNum into the data recovery spec table. AND in the data recovery spec table the J_RefNum is identified as the foreign key. It is too indicate the relationship between the two tables. Below is the code that I am trying to use in order to do this: $jRefNum = mysql_insert_id(); $sql="INSERT INTO dataRecSpec(J_RefNum, DRS_Name) VALUES ($jRefNum, $txt)"; Thanks