Using an ID array to perform a single db query

Question

I'm trying to perform a single query to the db, in which I am using an array of IDs to insert in the WHERE clause. So far I've had mixed results, and this is the closest I've gotten to achieve what I am looking for.

$prodId = array(17754, 17755, 17756);
$ids = implode(",",$prodId);

$qry = "SELECT clean_url AS url FROM xcart_clean_urls WHERE resource_id IN ($ids) ";
$resultTst = db_query($qry);

while ($result_row = db_fetch_array($resultTst)) {
   $urls[] = $result_row;
   if ( $urls ) {
      echo $urls[0]['url'] . '<br>';
      echo $urls[1]['url'] . '<br>';
      echo $urls[2]['url'] . '<br>';
   }
}

THE RESULTS I GET (I get the actual urls):
urlforid17754

urlforid17754
urlforid17755

urlforid17754
urlforid17755
urlforid17756

How can I perform the same functionality, but only retrieve the 3 urls once?

Thank you all.

You are trying to echo 3 rows each time through the loop. Move them out of the loop or just echo `$result_row`. If you don't need the echo then just use `$urls` after the loop. — AbraCadaver, Jun 06 '17 at 15:00
Try using distinct. `SELECT DISTINCT clean_url AS url FROM xcart_clean_urls WHERE resource_id IN ($ids)` — Andrew Nolan, Jun 06 '17 at 15:02
That was the problem in my logic...since the query was handling the "foreach" process, I wasn't really sure how to spit it out. If I remove the while(), it just echos Array. — Sergio, Jun 06 '17 at 15:07
What do you want to do, echo them or store them in a variable or both? — AbraCadaver, Jun 06 '17 at 15:07
The result is going to get retrieved by ajax, so whatever is the "cleanest". I like echo since I can see right on the page if it is working or not immediately. — Sergio, Jun 06 '17 at 15:09

AbraCadaver · Accepted Answer · 2017-06-06T15:16:58.400

2

You are trying to echo 3 rows each time through the loop. Move them out of the loop or just echo $result_row. If you don't need the echo then just use $urls after the loop. The URL seems to be in the url column so echo that:

while ($result_row = db_fetch_array($resultTst)) {
   echo $result_row['url'];
   $urls[] = $result_row;
}

//to see what $urls contains
print_r($urls);

I don't know what you want $urls to contain, but if you only want a single dimension then use this inside the loop instead:

   $urls[] = $result_row['url'];

edited Jun 06 '17 at 15:16

answered Jun 06 '17 at 15:09

AbraCadaver

78,200
7
66
87

Well, $urls will contain an array of urls that will be passed along via ajax to a js file. I'm assuming sending them as an array or even comma separated string in which I then can split them by the comma in js. – Sergio Jun 06 '17 at 15:20

Using an ID array to perform a single db query

1 Answers1