Manipulation MySQL data using PHP and PDO

Question

I am using PHP PDO and MySQL and would like to convert a MySQL table like this:

|Listing ID | Image ID |
|  1234     |     1    |
|  1234     |     2    |
|  1234     |     3    |
|  1235     |     1    |
|  1235     |     2    |

Into a PHP array like the one below, which I can parse through and insert into another MySQL table using PDO.

[url/path/1234-1:::url/path/1234-2:::url/path/1234-3, url/path/1235-1:::url/path/1234-2]

Here is the code I've written so far. I believe there is an issue with the way the while loops are working as it only picks up the first Listing string and causes an infinite loop.

//Set folder path
$target_path = realpath($_SERVER['DOCUMENT_ROOT']). "\path\uploads\\";

//Query to download all listing IDs in Markers table 
$query = $db->query("SELECT * FROM image");
$row = $query->fetch(PDO::FETCH_ASSOC);
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
$temp = $target_path.$row['L_ListingID']."-".$row['L_ImageID'].".jpg";
   //ListingID equals ListingID, append to array
   $LI = substr($temp, 40, 8);
   while($LI = $LI) {
    $temp = $temp.":::".$temp;
    echo $temp;
   }
}

This is the output which is an infinite loop. Notice that it starts at the second item in the table and hangs there:

C:\path\uploads\40532208-2.jpg:::C:\path\uploads\40532208-2.jpgC:\C:\path\uploads\40532208-2.jpg...ad infinitum

The escaping in string "\path\uploads\\" could be incorrect

neo
– neo

2012-12-27 03:55:04 +00:00
Commented Dec 27, 2012 at 3:55 — neo
– neo, Commented Dec 27, 2012 at 3:55

Abby Chau Yu Hoi · Accepted Answer · 2012-12-27 06:30:31Z

1

while($LI = $LI) causes the infinite loop.

I guess that is what you want

$target_path = realpath($_SERVER['DOCUMENT_ROOT']). "\path\uploads\\";
$query = $db->query("SELECT * FROM image");
while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
   $temp[] = $row;
}
foreach($temp as $i=>$v){
    $line = $v['L_ListingID']."-".$v['L_ImageID'].".jpg";
    $prod[] = $line.($temp[$i] == $temp[$i+1])?":::":",";
}

echo trim(implode($prod),",");

The code is not tested, but I think that the direction is like there.

edited Dec 27, 2012 at 6:30

answered Dec 27, 2012 at 3:58

Abby Chau Yu Hoi

1,4083 gold badges16 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

AME Over a year ago

This looks much better, but I need to add a function that compares the Listing IDs and groups similar Listing IDs together. That is why tried to include something like this: $LI = substr($temp, 40, 8);// gets Listing ID ... while($LI = $LI) // Compares ListingIDs, In my example.

Abby Chau Yu Hoi Over a year ago

What do you want to compare? $row['L_ListingID'] and what?

AME Over a year ago

If $row['L_ListingID'][0] == $row['L_ListingID'][1] then append $row['L_ListingID'][1] to $row['L_ListingID'][0] with ":::", and so on.

AME Over a year ago

/This is closer to what I am trying to accomplish $query2 = $db->query("SELECT * FROM image"); while ($row = $query2->fetch(PDO::FETCH_ASSOC)) { $temp[] = $row; } foreach($temp as $i=>$v){ $line = $target_path.$v['L_ListingID']."-".$v['L_ImageID'].".jpg"; if($v['L_ListingID'][$i] == $v['L_ListingID'][$i+1]){ $prod[] = $line.":::"; }else{ $prod[] = ","; } } echo implode("",$prod);

AME Over a year ago

The above code almost works perfectly, except it doesn't add a "," to the final string in the Listing ID set. Do you have a suggestion on how I can fix this?

|

Collectives™ on Stack Overflow

Manipulation MySQL data using PHP and PDO

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related