[SOLVED] Multiple listings
Moderator: General Moderators
Multiple listings
If I'm getting multiple (same) listings with a PHP search/results, is the bug in my MySQL/Recordset, or in the PHP?
OK, here's the code:
I want to try to figure this one out my self (with pointers obviously).
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>
?>join
You are searching through two tables so you need to join them some where in you query like
Otherwise you will end up with the problem you are having or worse.
Code: Select all
select a.userId, b.name FROM users a, details b WHERE a.userId=b.userIdOK,
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:
Am I doing th e JOIN right?
Steve
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
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
id's
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
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
I changed the MySQL part to the following after creating a new colum in the restaurants table.
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
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 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]SOLVED
Here's the code that ended up working. One of the problems I had was faulty data (a missing "The" for example).
feyd | Saltriver, [mysql_man] tag is for Mysql Manual lookup's, not posting a query
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