Pulling images from another table in to a PHP result

[sandydr]

My first post and I am not a programmer or familiar with the correct vocabulary associated with php. I thought I would give the following a go as it is the holidays and my php programmer is no where to be found.

 

I am ok at following scripts to get a simple result, unfortunately the listing manager I am using is coded with Zend. The system is built on a curve2.com and I am pretty much stuck with what exists. I would like to add some more php outputs, which I don't think are too complicated, but I can't seem to get my head around pulling information from multiple tables in to 1 result.

 

I am trying to pull all the data from a 'Listing' table in to 4 columns, which works ok. What I can not get around is trying to link up the 'Image' table to my 'Listing' table and pulling the main image in to my php output. There is a common 'id' but each 'id' has 4+ images and I would like to select the just main one and resize it smaller.

 

So far, I have the following, which I have put together from following examples on this website. 'id' is the unique id for each record.

 

 

<?

$query="select * from listings";

$result=mysql_query($query);

 

$cols=4;

echo "<table>";

do{

echo "<tr>";

for($i=1;$i<=$cols;$i++){

 

$row=mysql_fetch_array($result);

if($row){

$img = $row['image_path'];

?>

        <td>

            <table>

                <tr valign="top">

                    <td><img src="images/<?=$img ?>" /></td>

                    <td>

                        <b><a href="http://www.coralbayrealestate.com/search/show.php?id=<?=$row['id'] ?> "> <?=$row['title'] ?> </a> </b><br />

                        <?=$row['state'] ?><br />

                        $USD <?=number_format ($row['price']) ?><br />

                    </td>

                    <td width="20"> </td>

                </tr>

          </table>

        </td>

<?

}

else{

echo "<td> </td>";

}

}

} while($row);

echo "</table>";

?>

 

The image table, called 'images' has 3 fields

 

'id' - individual photos id's

'listid' - Id record to the listing table

'fname' - image name

 

Any guidance would be much appreciated on how to get the primary image on to the table.

Happy holidays and best wishes to all for 2011.

Sandydr

 

 

[the182guy]

What is the structure for the listings and images tables? I take it there is a field on the images table that is a flag to say if the image is the main image or not. You can use a SQL JOIN for what you need.

[sandydr]

Thanks for putting me on the right track.

 

I found a tutorial, but still do not quite understand the syntax to use. Modifying the query, it now looks like the below. I however get a 'Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/coralbay/www/www/allsearch/all.php on line 22' - Line 22 being $row=mysql_fetch_array($result);

 

 

 

<?

$query="select id, title, state, price, fname

FROM listings, images

join  listing.id = images.listid";

$result=mysql_query($query);

 

$cols=4;

echo "<table>";

do{

echo "<tr>";

for($i=1;$i<=$cols;$i++){

 

$row=mysql_fetch_array($result);

if($row){

$img = $row['fname'];

?>

        <td>

            <table>

                <tr valign="top">

                    <td><img src="images/<?=$img ?>" /></td>

                    <td>

                        <b><a href="http://www.coralbayrealestate.com/search/show.php?id=<?=$row['id'] ?> "> <?=$row['title'] ?> </a> </b><br />

                        <?=$row['state'] ?><br />

                        $USD <?=number_format ($row['price']) ?><br />

                    </td>

                    <td width="20"> </td>

                </tr>

          </table>

        </td>

<?

}

else{

echo "<td> </td>";

}

}

} while($row);

echo "</table>";

?>

 

 

I have attached a couple of pdf's that show the structure. I tried to copy and paste it in to here, but it was beyond me...!

Thanks for you help

Sandydr

 

 

 

 

[attachment deleted by admin]

[BlueSkyIS]

 

$result=mysql_query($query) or die(mysql_error() . " IN $query");

[sandydr]

Thanks Bluesky,

so the error is in the syntax, as I get the following result

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.listid' at line 3 IN select id, title, state, price, fname FROM listings, images JOIN listings.id = images.listid

 

I don't think I am using the correct form to join the tables. Anyone know a good tutorial on this?

Regards

Sandydr

[BlueSkyIS]

http://www.google.com/search?client=safari&rls=en&q=mysql+join+syntax&ie=UTF-8&oe=UTF-8

[the182guy]

The query should be something like

 

SELECT

  a.fname,

  b.id,

  b.title,

  b.state,

  b.price

FROM

  images a

LEFT JOIN

  listings b ON b.id = a.listid

WHERE

  a.type = 'main'

 

That would work if there is always a main image for each list record. Obviously I can't give you the exact query as you haven't given the exact table structure