CODEWITHSUNDEEP
phpsql
DLO
DLO

Reputation: 309

PHP C or store sql query in array

I have a query that gets a number of cols off my database

$searchResults = mysql_query("SELECT customerRefId, seekingAddressSuburb, 
  seekingAddressPostcode, seekingAddressState FROM customer_seeking");

Then I use the data to display a HTML table

?>
<table border="1">
<th>Customer Ref ID</th>
<th>Suburb</th>
<th>State</th>
<th>Postcode</th>
<?php
while($row=mysql_fetch_array($searchResults))
{
echo"<tr><td>$row[customerRefId]</td><td>$row[seekingAddressSuburb]</td><td>$row[seekingAddressPostcode]</td><td>$row[seekingAddressState]</td></tr>";
}
?>
</table>

What I need to do next is have another query as per below, but instead of hard coding 9,10 I want to use the $row[customerRefId] for the IN ()

Should I use mysql_data_seek($searchResults,0); to retun to the top of the sql results or should I build an arrary?

$searchResults2 = mysql_query("SELECT customerRefId, firstName, lastName, email, phone, mobile FROM customer_contact WHERE customerRefId IN (9,10)");

Upvotes: 1

Views: 74

Answers (1)

chrislondon
chrislondon

Reputation: 12031

Firstly as everyone is saying in the comments mysql_* functions are deprecated and you need to switch to mysqli.

To solve your problem what you need is a JOIN. With a JOIN you can get data from both tables combined. Here's an example:

SELECT 
    customerRefId,
    seekingAddressSuburb, 
    seekingAddressPostcode, 
    seekingAddressState,
    firstName,
    lastName,
    email,
    phone,
    mobile
FROM 
    customer_seeking cs
LEFT JOIN
    customer_contact cc ON cc.customerRefId = cs.customerRefId

Every time you loop through a row you will have all the data at once!

Upvotes: 1

Related Questions