...

View Full Version : Selecting from a position in an array...



thoford75
01-25-2012, 12:22 PM
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)




$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! :D

thoford75
01-25-2012, 12:25 PM
$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
01-26-2012, 05:32 AM
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


$myIdStr = implode(",",$mydata); this is assuming you are ONLY sending the ids

get all the records


$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
01-26-2012, 09:03 AM
Hi, thanks for the reply. Using this returns this:


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



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
01-26-2012, 09:18 AM
is that the serialized data stored in the database?

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

thoford75
01-26-2012, 09:24 AM
Yes the data is serialized. The entire code currently is:


$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
01-26-2012, 09:26 AM
prob easiest to get all the records and unserialize them and search for whatever record you are looking for

thoford75
01-26-2012, 09:38 AM
As a test I tried the following 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
01-26-2012, 12:05 PM
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
01-27-2012, 10:05 AM
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
01-27-2012, 11:36 AM
$someVar = 'a:2:{i:0;s:5:"Chair";i:1;s:5:"Table";} ';
print_r(unserialize($someVar));

thoford75
01-27-2012, 11:56 AM
Almost there! :) My entire 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
01-27-2012, 12:12 PM
do you know how to loop through an array and echo the results?

jmj001
01-27-2012, 12:13 PM
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
01-27-2012, 12:23 PM
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. :)

jmj001
01-27-2012, 04:07 PM
$thisArr = array("chair","table","bed");
$j = count($thisArr);
for($i = 0; $i < $j; $i++){
echo $thisArr[$i]."<br>";
}



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum