PHP C or store sql query in array

Question

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

YES! Keep using mysql_. In the meantime, may I ask what your website's URL is? — Cole Tobin
– Cole Tobin, Commented Jun 9, 2013 at 1:17
I think storing in an array would be a better choice, as it will be cheaper, faster and efficient to use an in memory data structure compared to fetching it again from database(which might require a disk I/O). — vidit
– vidit, Commented Jun 9, 2013 at 1:23
In general, I'd try to avoid having your web app switch control from PHP to the database, back to PHP, back to the database, back to PHP. Also, please take @ColeJohnson's suggestion above and use something else besides raw mysql_ calls to access your database. Maybe use PDO. See php.net/manual/en/pdo.query.php — cmt
– cmt, Commented Jun 9, 2013 at 1:24
if you used the same result set for MySQL then its better to use mysql_data_seek, mysql already used memory and placed the whole result for you, no need to duplicate into array — Hawili
– Hawili, Commented Jun 9, 2013 at 1:57

chrislondon · Accepted Answer · 2013-06-09 04:30:07Z

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!

Collectives™ on Stack Overflow

PHP C or store sql query in array

1 Answer 1

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related