Mysql, PHP help !!! PLEASE

[Bartman]

I hope someone can help me here. I have been stuck for days and search high and low for this.  I have a Mysql DB there is a table called "Listings" with a column called "Features" The items in the "features" columns are listed by CSV  So under the Column  the first row would say  25,13,28,3,7,12,9  etc.  Each of those numbers correspond to a column in another table called "FEATURES" The 2 columns under this table are  "Features" and "ID".  So the numbers I mentioned before match the numbers in ID. IE 25 means  CD PLAYER, 13 means SPEAKERS, 28 means WIRES etc.  When I try to display the data from the features row it only shows the FIRST number as text with a comma and none of the other ones.  So I can get it to show CD PLAYER with the comma but none of the others appear.  Like it is reading only the first number before the comma. Here is the code I am using:

 

<?php

 

$query = "SELECT listings.features, features.features ".

"FROM listings, features ".

"WHERE listings.features = features.id";

 

$result = mysql_query($query) or die(mysql_error());

 

while ($row = mysql_fetch_assoc($result))  {

    $features = explode(",",$row['features']);

    foreach ($features as $path) {

echo $path;

}

}

?>

 

Can someone please tell me why it is not showing the other items? 

[requinix]

Normalize your database. Get rid of the features column and replace it with a third table that relates listings with features. It will have two columns: one for the listing ID and one for the feature ID.

 

Once that's done you need only one SQL query with a couple JOINs to get everything you need.

[Psycho]

EDIT: Requinix beat me to it, but I'll leave this anyway as I think there is add'l information that might be of value.

 

You can't JOIN a single csv list in one table with individual IDs in another. MySQL is 'trying' to compare the two values in your JOIN statement. I'm guessing the features ID is of a numeric type (i.e. an int) whereas the listing.features value is likely a varchar. Therefore, MySQL is doing type conversion to do the comparison. I'm guessing it is converting the listing.features value to an int and only the first value is left.

 

You are going about this completely wrong. You should never store multiple values in a single field. It defeats the whole purpose of a relational database. You have a many-to-one relationship between the listings and feature. So, you need a third table to store the relationships.

 

The "listings" table should store a unique id (listing_id) along with the basic information of the listing

The "features" table should store a unique id (feature_id) along with the basic information of the feature

 

Then you would have a third table 'listing_features' to associate a listing to multiple features. You would have a single record for each feature for a listing. It would only need two columns 'listing_id' and 'feature_id'.

[Bartman]

well the mysql was already made that way and has like 500 entries already so I am guessing I cannot change it now.

 

The features.ID is an INT  but the listing.features value is a text type

 

So there is going to be no way I can display all the features then?

[Psycho]

No, of course you can. You can work around the problem with a bunch of hacks or you can make the necesasry changes to fix it the right way.

 

It would not be difficult to migrate the existing data to an appropriate structure. Just create the new table as indicated above. Then create a simple script to run through all the records one time to do the migration. But, you would also need to modify any scripts you currently have to add, edit, delete and read the data.

 

Step 1: Create the new table with columns for the listing id and the feature id.

Step 2: Create a script to get the listing id and the CSV features list from the current listings table. Process the recorsd and run an insert query to create all the new records in the new table.

Step 3: Rewrite your scripts to work with the new, correct design

Step 4: delete the features column in the listings table.

 

Here is a sample script to complete step 2

//Script to run ONE TIME to populate new records
$query = "SELECT id, features
          FROM listings";
$result = mysql_result($query) or die(mysql_error());

//Process results into insert value
$insertValues = array();
while($row = mysql_fetch_assoc($result))
{
    $listing_id = $row['id'];
    $features_ary = split(',', $row['features']);

    foreach($features_ary as $feature_id)
    {
        $insertValues[] = "($listing_id, $feature_id)"
    }
}

//Create and run INSERT query for all the records
$query = "INSERT INTO listing_features
              (`listing_id`, `feature_id`)
          VALUES " . implode(", ", $insertValues);
$result = mysql_result($query) or die(mysql_error());

[Bartman]

Thanks for you help mjdamato  I will try and redo it. But I do have one more question if you could help

 

I have another Table called  "listimages"  with 2 columns  one being  "imagepath"  other being  "listingid"

 

imagepath has all the images for each listing. and listingid shows the number that goes with the table Listing Auto increment number

 

SO:

 

imagespath      |    listingid

-----------------------------------

stereo1.jpg      |      1

stereo1a.jpg    |      1

stereo1b.jpg    |      1

stereo2.jpg      |      2

stereo2a.jpg    |      2

stereo2b.jpg    |      2

 

And so on.

 

How can I display them in a row like  stereo1.jpg, stereo1a.jpg, stereo1b.jpg

 

When  I tried it one way it would only show the first image. when I tried it a different way it showed them all but repeated other items I added over and over like this

for example lets say I added another $row to display say Name, then it cam up like this

 

sony stereo1.jpg

sony stereo1a.jpg

sony stereo1b.jpg

 

jvc stereo2.jpg

jvc stereo2a.jpg

jvc stereo2b.jpg

 

I want them to show like this:

 

sony  stereo1.jpg, stereo1a.jpg, stereo1b.jpg

jvc  stereo2.jpg, stereo2a.jpg, stereo2b.jpg

 

thanks alot mjdamato

 

[Psycho]

function listingImagesOutput($listingName, $imageList)
{
    return "$listingName " . implode(', ', $imageList) . "<br>\n";
}

$query = "SELECT l.listing_name, li.imagespath
          FROM Listings AS l
          JOIN listimages AS li ON l.listingid = li.listingid
          ORDER BY l.listingid";
$result = mysql_query($query);

$current_listing = false;
while($row = mysql_fetch_assoc($result))
{
    if($current_listing != $row['listing_name'])
    {
        if($current_listing != false)
        {
            //Output listing and images
            echo listingImagesOutput($current_listing, $imagesAry);
        }
        //Create temp array for images of this listing
        $imagesAry = array();
        //Set flag to check for change in listing
        $current_listing = $row['listing_name'];
    }
    //Add image path to temp array for current listing
    $imagesAry[] = $row['imagespath'];
}
//Output last listing and images
echo listingImagesOutput($current_listing, $imagesAry);

[Bartman]

Hi mjdamato  thanks for your help...that did not work though When I put <?php  in front and ?> at the end and add my DB info, I loaded the file and it came up

 

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in blah/blah/pic.php on line 19

 

Warning: implode() [function.implode]: Invalid arguments passed in blah/blah on line 9

 

I did notice you had imagespath  with an s  which I mistakenly put one in the post.  I changed them but still nothing. BUT  when i change the code and put it to this:

 

<?php

// Make a MySQL Connection
mysql_connect(REMOVED HERE)
mysql_select_db(REMOVED HERE)

function listingImagesOutput($listingName, $imageList)
{
    return "$listingName " . implode(', ', $imageList) . "<br>\n";
}

$query = "SELECT l.listingid, li.imagepath
          FROM listimages AS l
          JOIN listimages AS li ON l.listingid = li.listingid
          ORDER BY l.listingid";
$result = mysql_query($query);

$current_listing = false;
while($row = mysql_fetch_assoc($result))
{
    if($current_listing != $row['listingid'])
    {
        if($current_listing != false)
        {
            //Output listing and images
            echo listingImagesOutput($current_listing, $imagesAry);
        }
        //Create temp array for images of this listing
        $imagesAry = array();
        //Set flag to check for change in listing
        $current_listing = $row['listingid'];
    }
    //Add image path to temp array for current listing
    $imagesAry[] = $row['imagepath'];
}
//Output last listing and images
echo listingImagesOutput($current_listing, $imagesAry);
?>

 

All the images appear, they are separated by commas but are duplicated. like this. 

 

stereo1.jpg,stereo1.jpg,stereo1.jpg,stereo2.jpg,stereo2.jpg,stereo2.jpg,stereo3.jpg,,stereo4.jpg,stereo3.jpg,,stereo4.jpg,stereo3.jpg,,stereo4.jpg,stereo3.jpg,,stereo4.jpg,stereo5.jpg,,stereo5.jpg,stereo5.jpg,stereo5.jpg,stereo5.jpg,stereo5.jpg,stereo5.jpg,stereo5.jpg

 

It is random. it duplicated them first 3 pics are the same then next 3 same, then 1  then next to 4 then back to 1 then 6 of the same...lol strange.

 

Each listing has 6 images. I just want it to display the 6 images separated by commas then <br> to show the next 6. Cant I group the 6 images from the listingid together.

 

Let me know if I am doing something wrong it the code. Thanks again

[Psycho]

I do not have your database and you didn't even provide all the necessary field names, so I make assumptions on a lot of things. I provide code as a guide on how you can implement it and leave it to you to properly implement it.

 

I've looked at the code again and don't see any error that would cause the problems you state. This leads me to believe that either 1) You have duplicate image records for the listings or 2) the JOIN statement is using the wrong criteria and all the images records are being associated with all the listings. You should check your database for duplicates and you could also run the query in PHP admin to view the results to see if they look proper.

[Bartman]

Hi mjdamato  thanks again for your help...I checked for duplicates and nothing. Here is exactly what I am trying to do.  I have everything working. My site runs fine. all displays perfect.

 

I need to make a CSV of my listings table to import somewhere else.

 

I am simply using this PHP code to display the data so I can cut and paste it to a notepad and make it a CSV file.

 

All the columns I need are displaying properly now. Here is what I have and its probably all wrong but it is showing me what I need except the images at the end it shows all 6 images I need for each row. but it duplicates everything else.  Like this:

 

bob,sony,cdplayer,"image1.jpg"

bob,sony,cdplayer,"image2.jpg"

bob,sony,cdplayer,"image3.jpg"

bob,sony,cdplayer,"image4.jpg"

bob,sony,cdplayer,"image5.jpg"

bob,sony,cdplayer,"image6.jpg"

 

But I want it to look like this:

bob,sony,cdplayer,"image1.jpg,image1=2.jpg,image3.jpg,image4.jpg,image5.jpg,image6.jpg"

 

Here is the code

 

<?php

$result = mysql_query("SELECT * FROM listings, listimages WHERE listings.id = listimages.listingid ORDER BY listings.id DESC ")

or die(mysql_error());

echo "name,product,features,photo<br>";

// keeps getting the next row until there are no more to get
while ($row = mysql_fetch_assoc($result))  {
    $imagepath = explode(",",$row['imagepath']); 
    foreach ($imagepath as $path) { 
// Print out the contents of each row into a table 
echo $row['name'];
echo ",";
echo $row['product'];
echo ",";
echo $row['features'];
echo ",\"";
        echo "$path";
echo "\"";
echo "<br>";
   }
}

?>

 

"name", "product" and "features" columns are from the "listings" table and the images come from the "listimages". So they are like this:

 

listings

|  id  |  name  |  products  |  features  |

 

listimages

|  id  |  imagepath  |  mainimage  |  listingid  |

 

 

id (which are the auto_increments)

imagepath  (which shows the path/image1.jpg)

mainimage  (which just shows 0 or 1 depending on the picture that is the default for that listing, 1 being default)

listingid  (shows numbers 1 2 3  etc corresponding to the Id in the listings table to show what images go with what listing) There are up to 6 images for each listing.

 

Does that help at all?

[Psycho]

It is apparent from the code you just posted that you are storing the images in a comma separated list in a single field. Just as you shouldn't be doing that for feature IDs, you shouldn't be doing that for images either. The code I posted was based upon your table structure being 'correct'.

 

As for the code you posted based upon your current table structure, the solution is pretty obvious. You have the 'name', 'product' and 'features' values before you run the foreach loop, so why don't you echo them before the loop? That is why you are getting that data duplicated for each image. But you already have the images in a comma separated list. So why would you split the images based upon the comma and then run a foreach loop to echo them out comma separated?

 

You should really fix your DB design. But, this should work with your current structure

$query = "SELECT listings.name, listings.product, listings.features, listimages.imagepath
          FROM listings
          JOIN listimages ON listings.id = listimages.listingid
          ORDER BY listings.id DESC";
$result = mysql_query($query) or die(mysql_error());

echo "name,product,features,photo<br>";
// keeps getting the next row until there are no more to get
$flag = '';
while ($row = mysql_fetch_assoc($result))
{
    echo "{$row['name']},{$row['product']},{$row['features']},\"{$row['imagepath']}\"";
}