Page 1 of 1

table structure for php query

Posted: Wed Sep 02, 2009 9:09 am
by ijlal2
Hi all

I've a table 'photos' containing three fields.

photo_id, Restaurant name, image
1, Restaurant 1, restaurant 1 Image_1
2, Restaurant 1, restaurant 1 Image_2
3, Restaurant 1, restaurant 1 Image_3
4, Restaurant 2, restaurant 2 Image_1
5, Restaurant 2, restaurant 2 Image_2
6, Restaurant 2, restaurant 2 Image_3
7, Restaurant 3, restaurant 3 Image_1
8, Restaurant 3, restaurant 3 Image_2

basically it contains photos of restaurants.
Each row describe a photo. If a restaurant has multiple photos, it would have multiple rows.

Now i want to display a html table like this

Restaurant Name, Images
Restaurant 1, restaurant 1 Image_1,restaurant 1 Image_2,restaurant 1 Image_3

i.e a row containing restaurant name in one cell and ALL photos of that restaurant in single cell..

what i am doing is

Code: Select all

 
$query='select * from photos';
$res=mysql_query($query);
while($row=mysql_fetch_array($res)){
 
echo '<tr><td>'.$row['restaurant_name'].'</td><td>'.$row['image'].'</td></tr>';
 
 
}
echo '</table>';
 
But this get printed as
restaurant name, first image.
restaurant name, second image
restaurant name, third image.

How to get all photos of a restaurant in a cell?.

Regards

Re: table structure for php query

Posted: Thu Sep 03, 2009 2:01 am
by bala_1225
Hi plz follow this one... u will get what u want exactly....

Code: Select all

 
<?php
$con = mysql_connect('localhost','root','');
if(!$con)
{
    die('Could not connect : '.mysql_error());
}
mysql_select_db('res',$con);
 
[color=#BF0080][b]//to display photo_id, Restaurant_name uniquely..[/b][/color]
 
$selectQuery = "select DISTINCT photo_id, Restaurant_name from photos";
 
if (!mysql_query($selectQuery,$con))
{
  die('Error: ' . mysql_error());
}
$queryExecute = mysql_query($selectQuery,$con);
 
echo "<table width='600' border='1' align='center' cellpadding='0' cellspacing='0'>
      <tr>
        <th width='96' align='center'>Photo id </th>
        <th width='162' align='center'>Restaurent Name </th>
        <th width='342' align='left'>images</th>
      </tr>";
 
while($result = mysql_fetch_array($queryExecute))
{
    $photoId        = $result['photo_id'];
    $restaurantName = $result['Restaurant_name'];
    
    [color=#BF0080][b]//to select multiple images for a single restaurent..[/b][/color]
    
    $selectImageQuery = "select image from photos where photo_id=$photoId AND Restaurant_name='$restaurantName'";
    if (!mysql_query($selectImageQuery,$con))
    {
      die('Error: ' . mysql_error());
    }
    $queryExecuteImage = mysql_query($selectImageQuery,$con);
    
    echo "<tr>
        <td align='center'>".$photoId."</td>
        <td align='center'>".$restaurantName."</td>
        <td>";
    
    while($row = mysql_fetch_array($queryExecuteImage))
    {
        echo $row['image'].",";[b] [color=#BF0080]//it will display multiple images...[/color][/b]
    }
    
    echo "</td>
      </tr>";
}
echo "</table>";
?>
 
:lol:

Output:

Re: table structure for php query

Posted: Thu Sep 03, 2009 2:14 am
by VladSun
Easier and a single SQL query
http://dev.mysql.com/doc/refman/5.0/en/ ... oup-concat

@ijlal2
@bala_1225

Please, use [ php ] [/ php] (without spaces) BB code tags to hilight your PHP code.

Re: table structure for php query

Posted: Thu Sep 03, 2009 4:26 am
by bala_1225
hi VladSun.. thanks for your suggestion... now i got the query..correctly it reduces the coding...

Re: table structure for php query

Posted: Thu Sep 03, 2009 4:29 am
by bala_1225
Hi ijlal2 u can try this query it makes easier......and short... i modified now.....

Code: Select all

 
<?
$con = mysql_connect('localhost','root','');
if(!$con)
{
    die('Could not connect : '.mysql_error());
}
mysql_select_db('flash_quiz',$con);
 
$selectQuery =  "[color=#BF00FF][size=150][b]SELECT photo_id, Restaurant_name, GROUP_CONCAT(image) as images FROM photos GROUP BY photo_id[/b][/size][/color]";
 
 
if (!mysql_query($selectQuery,$con))
{
  die('Error: ' . mysql_error());
}
$queryExecute = mysql_query($selectQuery,$con);
 
echo "<table width='600' border='1' align='center' cellpadding='0' cellspacing='0'>
      <tr>
        <th width='96' align='center'>Photo id </th>
        <th width='162' align='center'>Restaurent Name </th>
        <th width='342' align='left'>images</th>
      </tr>";
while($result = mysql_fetch_array($queryExecute))
{
    $photoId        = $result['photo_id'];
    $restaurantName = $result['Restaurant_name'];
    $image          = $result['images'];
    echo "<tr>
        <td align='center'>".$photoId."</td>
        <td align='center'>".$restaurantName."</td>
        <td>".$image."</td>
      </tr>";
}
echo "</table>";
?>

Re: table structure for php query

Posted: Fri Sep 04, 2009 7:52 am
by ijlal2
@ VladSun .. Excellent .. great..

@Bala ... Thanks alot.

Thanks alot.
Regards.

Re: table structure for php query

Posted: Sat Sep 05, 2009 3:42 am
by Benjamin
Please use the appropriate

Code: Select all

 [ /code] tags when posting code blocks in the forums. Functions in your code will be automatically linked to manual entries and your code will be syntax highlighted making it much easier for everyone to read. You will most likely receive more answers as well.

If you are new to the forums, please be sure to read:

[list=1][*][url=http://forums.devnetwork.net/viewtopic.php?t=30037]Forum Rules[/url]
[*][url=http://forums.devnetwork.net/viewtopic.php?t=8815]General Posting Guidelines[/url]
[*][url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/list]

[quote="Forum Rules"]
11. Please use proper, complete spelling when posting in the forums. AOL Speak, leet speak and other abbreviated wording can confuse those that are trying to help you (or those that you are trying to help). Please keep in mind that there are many people from many countries that use our forums to read, post and learn. They do not always speak English as well as some of us, nor do they know these aberrant abbreviations. Therefore, use as few abbreviations as possible, especially when using such simple words.
[/quote]