[SOLVED] Query only returns last item

[Lassie]

I need to search a product file and retireve multiple links.
I have checked my code has multiple product codes, but my query seems to only return the last item.
I want to return the result as an array from which I can then build links to download folders.
My code is:-
[code]
$dl_link=display_dl_link2($_SESSION['cart'],'product_id');

function display_dl_link2($cart,$product_id)

{
foreach ($cart as $product_id => $qty)
{
echo "The value of $product_id is $qty<br />";//debug to check have prod ids shows
}
//The value of 2 is 1
//The value of 3 is 1


  if (!$product_id || $product_id=='')
  {
    return false;
    }
$connection = db_connect();   
  $query = "select dl_link from products where product_id='$product_id'";
  $result = mysql_query($query);
  if (!$result)
  {
mysql_error();
return false;
}

else {
$result = mysql_fetch_assoc($result);
  return $result;
{
}
}
}
//results to array function
function db_result_to_array($result)
{
  $res_array = array();

  for ($count=0; $row = mysql_fetch_assoc($result); $count++)
  $res_array[$count]=$row;
    return $res_array;
}
[/code]
used print_r to check contents of $dl_link which shows
Array
(
    [dl_link] => 31 days to bigger arms.pdf
)

I have also checked the database has the fields populated.
Any help much appreciated.

[HuggieBear]

You're redefining the $result resource by mistake with this line...

[code=php:0]$result = mysql_fetch_assoc($result);[/code]

Change it to something like:

[code=php:0]$row = mysql_fetch_assoc($result);[/code]

Regards
Huggie

[Lassie]

Hi, Thanks for coming back.
I afraid I made a mistake in the code I posted. The code should read following the query.
[code]
else {
$result = db_result_to_array($result);
  return $result;
[/code]
running this I get this from my print_r
Resource id #15

[HuggieBear]

OK, give this a try...

[code]
<?php
$dl_link = display_dl_link2($_SESSION['cart'], 'product_id');

function display_dl_link2($cart,$product_id){
  foreach ($cart as $product_id => $qty){
      echo "The value of $product_id is $qty<br />"; //debug to check have prod ids shows
  }
  //The value of 2 is 1
  //The value of 3 is 1

  if (!$product_id || $product_id==''){
      return false;
  }

  $connection = db_connect();   
  $query = "select dl_link from products where product_id='$product_id'";
  $result = mysql_query($query);
  if (!$result){
      mysql_error();
      return false;
  }
  else {
      $result_array = db_result_to_array($result);
      return $result_array;
  }
}

//results to array function
function db_result_to_array($result_resource){
  $res_array = array();

  while ($row = mysql_fetch_array($result_resource, MYSQL_ASSOC)){
      $res_array[] = $row;
      return $res_array;
  }
}
?>
[/code]

Regards
Huggie

[Lassie]

Ok. I tried that and get the same result eg
The value of 2 is 2
The value of 3 is 1

Array
(
    [0] => Array
        (
            [dl_link] => 31 days to bigger arms.pdf
        )

)

The other problem is that the result to array is used eleswhere so for this should it be called something else?
Thank you for your help

[HuggieBear]

OK, if I understand the code correctly, then it's over complicating itself and acting rather strangely...

Why are you passing the string '[color=red]product_id[/color]' into the [color=blue]display_dl_link2()[/color] function when you straight away overwrite it with a different value?

Regards
Huggie

[Lassie]

I am passing the product id so that i can select all the links that correspond the all the products passed.
I think you are right. There is something wrong at this point.
If i count the rows returned from the query I only get one.

I put this in
[code]
$query = "select dl_link from products where product_id='$product_id'";
  $result = mysql_query($query);
  $numRows = mysql_num_rows($result);
  echo "$numRows";
[/code]

debugs
show
The value of 2 is 1
The value of 3 is 1
1//numof rows returned
Array
(
    [0] => Array
        (
            [dl_link] => 31 days to bigger arms.pdf
        )

)
When you say overwritten could you explain.I think this is my problem.
Thanks

[Lassie]

I had some further thoughts on my problem.
I know that my var $cart contains the product_ids and that is what ineed to query the db.
Therefore I have used the array_keys() and the list() to try and get a var containing th eproduct values.
the modfifcation is
[code]
function display_dl_link2($cart)

{
echo '<pre>';
print_r ($cart);
echo '</pre>';

$keys=array_keys($cart);

echo '<pre>';
print_r ($keys);
echo '</pre>';
list($product_id)=$keys;
echo "$product_id";
$connection = db_connect();   
  $query = "select dl_link from products where product_id='$product_id'";
  $result = mysql_query($query);
  $numRows = mysql_num_rows($result);
  echo "$numRows";
  if (!$result)
  {
mysql_error();
return false;
}
else {

    $result = db_result_to_array($result);
      return $result;

}
echo"<br /><br />";
}

$connection = db_connect();   
  $query = "select dl_link from products where product_id='$product_id'";
  $result = mysql_query($query);
  $numRows = mysql_num_rows($result);
  echo "$numRows";
  if (!$result)
  {
mysql_error();
return false;
}
else {

    $result = db_result_to_array($result);
      return $result;

}
}
[/code]
Unfortunately I am no further forward.
My de bug shows
Array
(
    [2] => 1
    [6] => 1
)
Array
(
    [0] => 2
    [1] => 6
)
2//the value of the first prod id

1//no of rows returned from query

I would be very grateful for any advice .

[HuggieBear]

OK, let's do away with the multiple functions and go for the code direct and see what happens... I'll put some comments and debug in there too.

[code]<?php
// Firstly check there's something in your session variable *debug*
print_r($_SESSION['cart']);

// Lets put each of the product_id's (pids) into a new array with single quotes around them
foreach ($_SESSION['cart'] as $pid => $qty){
  $pid_array[] = "'$pid'";
}

//makes the query easier if we put the pid's in a list
$pid_string = implode(', ', $pid_array);
print_r($pid_string); // should give us a list in the format 'id1', 'id2', 'id3' etc *debug*

// OK, let's do the database stuff
$connection = db_connect(); //connect
$sql = "SELECT product_id, dl_link FROM products WHERE product_id IN ($pid_string) ORDER BY product_id";
$result = mysql_query($sql) or die ("Sorry, unable to execute $sql: " . mysql_error()); // execute the query

// Loop through the results array, putting the values in to a new array keyed on pid
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $links[$row['product_id']] = $row['dl_link']; // Here's the magic
}

// Lets see if the array is as we expected *debug*
print_r($links);
?>[/code]

Let me know how you get on with that.

Regards
Huggie

[Lassie]

Hi Huggie,
Thanks for that. I still seem to get only one return item.
This is the debug line
Array ( [2] => 1 [6] => 1 ) '2', '6'Array ( [2] => Salad Recipes.pdf [6] => How to Buy a Car With Little or no credit.pdf )
[2] is the correct prod id for Salad etc but not the file.
the file is in the record for product 6.
the products 2 & 6 were the ones selected so thats Ok.
Dont think the pids were listed as planned.
I have shown the table structure below.
The product_id is the primary.
Thank you for your continued interest and help.

Field  Type  Null  Default
product_id  int(4) No 
cat_id  int(3) No  0
title  varchar(60) No 
product_desc  varchar(255) No 
price  decimal(6,2) No  0.00
pix  varchar(30) No 
type  varchar(20) No 
added_date  date No  0000-00-00
author  varchar(40) No 
isbn  varchar(13) No 
Featured  int(1) Yes  NULL
New  int(1) Yes  NULL
business  int(1) Yes  NULL
promo_link  varchar(60) No 
dl_link  varchar(60) No 

[Lassie]

Hi Huggie,
Sorry I'm going mad.The result is as expected.I just read it wrongly.
Many thanks for your help. I will study your approach and see what I can learn from it.
Lassie

[HuggieBear]

No problem, I simplified the code a little for you too.

[code]<?php
// Firstly check there's something in your session variable *debug*
print_r($_SESSION['cart']);

//makes the query easier if we put the pid's in a list
$pid_string = implode("', '", array_keys($_SESSION['cart']));
print_r($pid_string); // should give us a list in the format 'id1', 'id2', 'id3' etc *debug*

// OK, let's do the database stuff
$connection = db_connect(); //connect
$sql = "SELECT product_id, dl_link FROM products WHERE product_id IN ('$pid_string') ORDER BY product_id";
$result = mysql_query($sql) or die ("Sorry, unable to execute $sql: " . mysql_error()); // execute the query

// Loop through the results array, putting the values in to a new array keyed on pid
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)){
  $links[$row['product_id']] = $row['dl_link']; // Here's the magic
}

// Lets see if the array is as we expected *debug*
print_r($links);
?>[/code]

Huggie

[Lassie]

Thanks Huggie.
I have now completed my cart as a basic end to end job and now need to tidy up ( a lot)
and add some better security/checking.
thanks again
Lassie