Page 1 of 1

Multiple listings

Posted: Thu Sep 23, 2004 11:08 pm
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?

Posted: Thu Sep 23, 2004 11:32 pm
by feyd
how would we know? we don't know your query, database and table structure, and your code...

Posted: Fri Sep 24, 2004 12:01 am
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).

Posted: Fri Sep 24, 2004 12:05 am
by feyd
your query is the problem.

Posted: Fri Sep 24, 2004 12:17 am
by saltriver
Thanks.
I go geek on that a while.

join

Posted: Fri Sep 24, 2004 3:17 am
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.

Posted: Mon Sep 27, 2004 1:34 am
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

Posted: Mon Sep 27, 2004 3:35 am
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.

id's

Posted: Mon Sep 27, 2004 3:50 am
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

Posted: Tue Sep 28, 2004 5:06 pm
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?

Posted: Wed Sep 29, 2004 1:36 am
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]

SOLVED

Posted: Thu Sep 30, 2004 1:12 pm
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