table structure for php query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ijlal2
Forum Newbie
Posts: 6
Joined: Sun Jul 06, 2008 2:06 pm

table structure for php query

Post 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
Last edited by Benjamin on Sat Sep 05, 2009 3:41 am, edited 1 time in total.
Reason: Added [code=php] tags.
User avatar
bala_1225
Forum Commoner
Posts: 29
Joined: Tue Jul 28, 2009 3:20 am
Location: chennai,india

Re: table structure for php query

Post 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:
Attachments
out for this one..........
out for this one..........
file.png (47.94 KiB) Viewed 921 times
Last edited by Benjamin on Sat Sep 05, 2009 3:41 am, edited 2 times in total.
Reason: Added [code=php] tags.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: table structure for php query

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
bala_1225
Forum Commoner
Posts: 29
Joined: Tue Jul 28, 2009 3:20 am
Location: chennai,india

Re: table structure for php query

Post by bala_1225 »

hi VladSun.. thanks for your suggestion... now i got the query..correctly it reduces the coding...
User avatar
bala_1225
Forum Commoner
Posts: 29
Joined: Tue Jul 28, 2009 3:20 am
Location: chennai,india

Re: table structure for php query

Post 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>";
?>
Last edited by Benjamin on Sat Sep 05, 2009 3:43 am, edited 1 time in total.
Reason: Added [code=php] tags.
ijlal2
Forum Newbie
Posts: 6
Joined: Sun Jul 06, 2008 2:06 pm

Re: table structure for php query

Post by ijlal2 »

@ VladSun .. Excellent .. great..

@Bala ... Thanks alot.

Thanks alot.
Regards.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: table structure for php query

Post 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]
Post Reply