[SOLVED] Multiple listings

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
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Multiple listings

Post by saltriver »

If I'm getting multiple (same) listings with a PHP search/results, is the bug in my MySQL/Recordset, or in the PHP?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

how would we know? we don't know your query, database and table structure, and your code...
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Post by saltriver »

OK, here's the code:

Code: Select all

<?php
<?php require_once('../../Connections/hungryusaconnection.php'); ?>
<?php
$colname_rsmenu = "1";
if (isset($HTTP_GET_VARS['namefield'])) {
  $colname_rsmenu = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['namefield'] : addslashes($HTTP_GET_VARS['namefield']);
}
mysql_select_db($database_hungryusaconnection, $hungryusaconnection);
$query_rsmenu = sprintf("SELECT menus.id, menus.item, menus.restaurant, menus.description, restaurants.website, restaurants.webbutton FROM menus, restaurants WHERE item LIKE '%%%s%%' ORDER BY item ASC", $colname_rsmenu);
$rsmenu = mysql_query($query_rsmenu, $hungryusaconnection) or die(mysql_error());
$row_rsmenu = mysql_fetch_assoc($rsmenu);
$totalRows_rsmenu = mysql_num_rows($rsmenu);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../css/hungrycss01.css" rel="stylesheet" type="text/css">
<link href="../css/hungrycss02.css" rel="stylesheet" type="text/css">
<link href="../css/links01.css" rel="stylesheet" type="text/css">
</head>

<body>
<?php do { ?>
<table width="400" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="20"><?php echo $row_rsmenu['id']; ?></td>
    <td width="300"><span class="bigbrown"><?php echo $row_rsmenu['item']; ?></span><br>
      <span class="medblack"><?php echo $row_rsmenu['restaurant']; ?></span><br>
    <span class="smallblacknorm"><?php echo $row_rsmenu['description']; ?></span></td>
    <td width="80"><a href="<?php echo $row_rsmenu['website']; ?>" target="_blank"><?php echo $row_rsmenu['webbutton']; ?></a></td>
  </tr>

</table>
<?php } while ($row_rsmenu = mysql_fetch_assoc($rsmenu)); ?>
<?php
mysql_free_result($rsmenu);
?>
</body>
</html>



?>
I want to try to figure this one out my self (with pointers obviously).
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

your query is the problem.
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Post by saltriver »

Thanks.
I go geek on that a while.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

join

Post by phpScott »

You are searching through two tables so you need to join them some where in you query like

Code: Select all

select a.userId, b.name FROM users a, details b WHERE a.userId=b.userId
Otherwise you will end up with the problem you are having or worse.
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Post by saltriver »

OK,
I've done some geeking. Thanks for the Join suggestion. I had a feeling that's what I needed to do, though I'm not sure if I've done it right.
I'm getting individual results (instead of multiples) which solves the main problem. Two new problems arose out of this. I'm joining 3 columns from the restaurant table and trying to join them to the menu table, using the .restaurant that the two tables have in common.
Problem one: website (which is a url) and webutton (graphic) are not showing up.
Problem two: When I add the join, the name of the restaurant disappears, making me think its a bad idea to have identically named columns in different tables.

Here's the URL if you'd like to see. use the search by keyword.
http://www.hungryburlington.com/testing/

Here's the page:

Code: Select all

<?php

<?php require_once('../../Connections/hungryusaconnection.php'); ?>
<?php
$colname_rsmenu = "1";
if (isset($HTTP_GET_VARS['namefield'])) {
  $colname_rsmenu = (get_magic_quotes_gpc()) ? $HTTP_GET_VARS['namefield'] : addslashes($HTTP_GET_VARS['namefield']);
}
mysql_select_db($database_hungryusaconnection, $hungryusaconnection);
$query_rsmenu = sprintf("SELECT menus.id, menus.item, menus.restaurant, menus.description, menus.price_lg, restaurants.restaurant, restaurants.website, restaurants.webbutton FROM menus left join restaurants on restaurants.restaurant = menus.restaurant WHERE menus.item LIKE '%%%s%%' ORDER BY item ASC", $colname_rsmenu);
$rsmenu = mysql_query($query_rsmenu, $hungryusaconnection) or die(mysql_error());
$row_rsmenu = mysql_fetch_assoc($rsmenu);
$totalRows_rsmenu = mysql_num_rows($rsmenu);
?>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="../css/hungrycss01.css" rel="stylesheet" type="text/css">
<link href="../css/hungrycss02.css" rel="stylesheet" type="text/css">
<link href="../css/links01.css" rel="stylesheet" type="text/css">
</head>

<body>
<?php do { ?>
<table width="400" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td width="20" class="smallblacknorm"><?php echo $row_rsmenu['id']; ?></td>
    <td width="300"><span class="bigbrown"><?php echo $row_rsmenu['item']; ?></span><br>
      <span class="medblack"><?php echo $row_rsmenu['restaurant']; ?></span><br>
    <span class="smallblacknorm"><?php echo $row_rsmenu['description']; ?></span></td>
    <td width="80"><a href="<?php echo $row_rsmenu['website']; ?>"><?php echo $row_rsmenu['webbutton']; ?></a></td>
  </tr>

</table>
<?php } while ($row_rsmenu = mysql_fetch_assoc($rsmenu)); ?>
<?php
mysql_free_result($rsmenu);
?>
</body>
</html>




?>

Am I doing th e JOIN right?

Steve
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

When you get problems like this I would suggest you forget the php part. Try just running your query in SQL (either from the command line or some other interface). You can try this easily until you get the results you want, then go back to php and check how it handles things.
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

id's

Post by phpScott »

without knowing your db structure it looks like restaurant in both tables is the name. if that is the case you are better off putting a id field in the restuarant table and putting it as a foreign key in the menu's table so you can do a join on the restaurant id's instead of the 'name'
The problem with the resaurant part not showing up is that you have a table name and a field name both using restuarant which is quite confusing.


phpScott
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Post by saltriver »

OK, I figured that might be why the restaurant disappeared (wto columns of the same name), but what about website and webbutton? If I fix the restaurant problem will that fix the JOIN?
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

Post by saltriver »

I changed the MySQL part to the following after creating a new colum in the restaurants table.

Code: Select all

SELECT menus.id, menus.item, menus.restaurant, menus.description, menus.price_lg, restaurants.rest_name, restaurants.website, restaurants.webbutton FROM menus left join restaurants on restaurants.rest_name = menus.restaurant WHERE menus.item LIKE '%%%s%%' ORDER BY item ASC", $colname_rsmenu);
The restaurants are still disappearing. Do I need to change the name of one of the existing tables?

The website and buttons came up NULL, making me think my join isn't working.


feyd | fixed [mysql_man] tag to

Code: Select all

tag.[/color]
saltriver
Forum Commoner
Posts: 59
Joined: Fri Mar 12, 2004 2:40 pm
Location: Burlington, VT

SOLVED

Post by saltriver »

Here's the code that ended up working. One of the problems I had was faulty data (a missing "The" for example).

Code: Select all

SELECT menus.id, menus.item, menus.restaurant, 
menus.description, menus.price_lg, restaurants.rest_name, 
restaurants.website, restaurants.webbutton FROM menus inner join 
restaurants on restaurants.rest_name = menus.restaurant WHERE 
(menus.restaurant LIKE '%%%s%%' OR  menus.description  LIKE 
'%%%s%%'  OR menus.item LIKE '%%%s%%'  OR menus.sub_item LIKE 
'%%%s%%' OR menus.sub_1 LIKE '%%%s%%') ORDER BY RAND()

feyd | Saltriver, [mysql_man] tag is for Mysql Manual lookup's, not posting a query
Post Reply