Selecting from a position in an array...

[thoford75]

Hi!

I have a mysql database with 4 rows containing arrays, including an unique ID field.

I want to run a SELECT query to pull out the data from each array based on its position.

E.g. I may want to select from the 4 arrays in my row the 3rd results:

| ID | Name | Description | Price |
3 Car Red $100

My code so far looks like this: ($furnaccept is POST'd from the previous page)

PHP Code:


$furnaccept = serialize($_POST['furnaccept']);

$mydata = unserialize($furnaccept);

$itemcount = count($mydata);

for ($i=0;$i<$itemcount;$i++) {
echo '<br><br><b> SHOW ME ROW ' . $mydata[$i] . ' OF THE ARRAY:</b><br>'; 
echo '<br><br>';

$query = "SELECT furn_item,furn_desc,furn_qty,furn_cost 
FROM dbQuotes 
WHERE 
furn_ID='$i'";

$result = mysql_query($query);

while(list(
$furn_item,
$furn_desc,    
$furn_qty,
$furn_cost) = mysql_fetch_row($result))

{

echo $furn_item . ' <br />' . $furn_desc . ' <br />' .    $furn_qty . ' <br />' .    $furn_cost; 
     
}


} 


Am I on the right lines?

Help!

[thoford75]

Originally Posted by thoford75

PHP Code:


$query = "SELECT furn_item,furn_desc,furn_qty,furn_cost 
FROM dbQuotes 
WHERE 
furn_ID='$i'"; 


This doesn't work because $i will equal an id e.g.
0,1,2 ...

but my furn_ID row will look something like this:
a:3:{i:0;s:1:"0";i:1;s:1:"2";i:2;s:1:"3";}

Guess it's a case of converting the furn_ID data first??

[jmj001]

you should go to the database only once not for every iteration of a loop...

write all your id's into a comma separated string

PHP Code:

$myIdStr = implode(",",$mydata); 


this is assuming you are ONLY sending the ids

get all the records

PHP Code:

$query = "SELECT furn_item,furn_desc,furn_qty,furn_cost 
FROM dbQuotes 
WHERE 
furn_ID IN (".$myIdStr.")" 


then loop through the returned results and display...

[thoford75]

Hi, thanks for the reply. Using this returns this:

PHP Code:

SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE furn_ID IN (0,1) 


a:2:{i:0;s:5:"Chair";i:1;s:5:"Table";}
a:2:{i:0;s:17:"Brown with 4 legs";i:1;s:14:"Brown circular";}
a:2:{i:0;s:1:"4";i:1;s:1:"1";}
a:2:{i:0;s:3:"200";i:1;s:3:"400";}

PHP Code:

SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE furn_ID IN (0,1) 


a:2:{i:0;s:5:"Chair";i:1;s:5:"Table";}
a:2:{i:0;s:17:"Brown with 4 legs";i:1;s:14:"Brown circular";}
a:2:{i:0;s:1:"4";i:1;s:1:"1";}
a:2:{i:0;s:3:"200";i:1;s:3:"400";}

What I want to show is:

Chair
Brown with 4 legs
4
200

Table
Brown circular
1
400

So basically I want the SELECT command to look up an array's id then go to the database and pull out the value for that array position...

[jmj001]

is that the serialized data stored in the database?

you should be able to loop through the results and unserialize it

[thoford75]

Yes the data is serialized. The entire code currently is:

PHP Code:

 $mydata = unserialize($furnaccept);


$itemcount = count($mydata);

$myIdStr = implode(",",$mydata);  
for ($i=0;$i<$itemcount;$i++) {
echo '<br><br><b> SHOW ME ROW ' . $mydata[$i] . ' OF THE ARRAY:</b><br>'; 
  echo '<br><br>';
echo $query = "SELECT furn_item,    furn_desc,    furn_qty,    furn_cost FROM dbQuotes WHERE id='$quote_id' and furn_ID IN (".$myIdStr.")" ;
$result = mysql_query($query);
while(list($furn_item,    $furn_desc,    $furn_qty,    $furn_cost) = mysql_fetch_row($result))
{

echo '<br />' . $furn_item . ' <br />' . $furn_desc . ' <br />' .    $furn_qty . ' <br />' .    $furn_cost;      
}


} 


[jmj001]

prob easiest to get all the records and unserialize them and search for whatever record you are looking for

[thoford75]

As a test I tried the following code:

PHP Code:

echo '<br />' . unserialize($furn_item) . ' <br />' . $furn_desc[$i] . ' <br />' .    $furn_qty[$i] . ' <br />' .    $furn_cost; 


and output was:

SHOW ME ROW 0 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)
Array
a
a
a:2:{i:0;s:3:"200";i:1;s:3:"400";}

SHOW ME ROW 1 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)
Array
:
:
a:2:{i:0;s:3:"200";i:1;s:3:"400";}

[thoford75]

Had a thought... essentially I am trying to find the position of a value in an array. If i have the position (i.e. 0,1 or 0,3,4 etc) is there a way of saying

SELECT x from table where myID = $myID

THEN...

get position 0->
then position 1->

or

get position 0->
then position 3->
then position 4->


Help! ?

[thoford75]

How do I got from getting data like this:

a:2:{i:0;s:5:"Chair";i:1;s:5:"Table";}

into an array like this:

[0]-> Chair
[1]-> Table


?

[jmj001]

PHP Code:

$someVar = 'a:2:{i:0;s:5:"Chair";i:1;s:5:"Table";} ';
print_r(unserialize($someVar)); 


[thoford75]

Almost there! My entire PHP code:

PHP Code:

<?PHP 
include "../includes/connect.php";
$quote_id = $_POST['quote_id'];

$furnaccept = serialize($_POST['furnaccept']);

$mydata = unserialize($furnaccept);

$itemcount = count($mydata);

$myIdStr = implode(",",$mydata);
  
for ($i=0;$i<$itemcount;$i++) {
echo '<br><br><b> SHOW ME ROW ' . $mydata[$i] . ' OF THE ARRAY:</b><br>'; 
  echo '<br><br>';
echo $query = "SELECT furn_item,    furn_desc,    furn_qty,    furn_cost FROM dbQuotes WHERE id='$quote_id' and furn_ID IN (".$myIdStr.")" ;
$result = mysql_query($query);
while(list($furn_item,    $furn_desc,    $furn_qty,    $furn_cost) = mysql_fetch_row($result))
{

echo '<br />';
print_r(unserialize($furn_item)); 
echo '<br />';
print_r(unserialize($furn_desc)); 
echo '<br />';
print_r(unserialize($furn_qty)); 
echo '<br />';
print_r(unserialize($furn_cost)); 
echo '<br />';
 
}


}

?>

The result:


SHOW ME ROW 0 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)
Array ( [0] => Chair [1] => Table )
Array ( [0] => Brown with 4 legs [1] => Brown circular )
Array ( [0] => 4 [1] => 1 )
Array ( [0] => 200 [1] => 400 )


SHOW ME ROW 1 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)
Array ( [0] => Chair [1] => Table )
Array ( [0] => Brown with 4 legs [1] => Brown circular )
Array ( [0] => 4 [1] => 1 )
Array ( [0] => 200 [1] => 400 )


***

What I want...

SHOW ME ROW 0 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)
Chair
Brown with 4 legs
4
200


SHOW ME ROW 1 OF THE ARRAY:


SELECT furn_item, furn_desc, furn_qty, furn_cost FROM dbQuotes WHERE id='2' and furn_ID IN (0,1)

Table
Brown circular
1
400

[jmj001]

do you know how to loop through an array and echo the results?

[jmj001]

why are you storing this information in a serialized format, it doesn't make any sense to me... just put it i there the way you want it to come out

[thoford75]

Originally Posted by jmj001

do you know how to loop through an array and echo the results?

No, not for an array. Also the data is serialised as an array from a previous form.