Page 1 of 1
PHP include for a common query
Posted: Mon Aug 07, 2006 11:01 pm
by saltriver
Hi All,
I have a series of pages that have nearly identicle queries. The only things that change are the cities and counties. I'm trying to make pages where I declare variables defining the city and county at the top of the page, then php include the file with all of the common queries. At first I though I might need to make the city and county variables global so they could be within the scope of the included files, but from what I've read here and elsewhere, putting the variable in a session, which is alrady global, would be the way to go.
Presently, I'm simply declaring my variable like so:
I can reference the variable while the query is on the smae page just fine, but when I put it in another file and do the old PHP include, the query returns empty.
After I start the session, do I just declare
Code: Select all
$_SESSION['city'] = 'Springfield';
on the main page, then include the common query page as an include?
I'm guessing there are a few different ways to do this, but the end goal is to make this series of pages easy to replicate to apply to different cities.
Any thoughts?
Posted: Mon Aug 07, 2006 11:49 pm
by Weirdan
show us the content of your include file please
Posted: Tue Aug 08, 2006 12:40 am
by RobertGonzalez
Includes should not affect that at all. Include basically takes another file and makes it part of the calling file... essentially the same thing as if the file that was being included was part of the file to begin with.
Can you show some of the code that you suspect is giving you problems?
Posted: Tue Aug 08, 2006 12:40 am
by saltriver
Part 1:
Code: Select all
$this_city = 'Concord';
$this_county = 'Essex';
Part 2:
Code: Select all
mysql_select_db($database_dbname, $dbname;
$query_rs_rest = "SELECT * FROM business WHERE package=1 AND city = $this_city ORDER BY RAND()";
$rs_rest = mysql_query($query_rs_rest, $dbname) or die(mysql_error());
$row_rs_rest = mysql_fetch_assoc($rs_rest);
$totalRows_rs_rest = mysql_num_rows($rs_rest);
I'm trying to keep Part 1 on the index.php page and php include Part 2 into it. If it matters for this discussion, the Part 2 page will also have some html. Part 2 will have a series of queries based on which city is declared. The parts work fine together when they're coded on the same page. It breaks when I try to separate them using php include.
Posted: Tue Aug 08, 2006 12:54 am
by RobertGonzalez
Where you include the second page will have a dramatic effect on how they are processed. For example, since the second page has your queries, if you call it before you set the vars in page 1, the queries will return 0 results. Also, for clarity, your query should be
Code: Select all
$query_rs_rest = "SELECT * FROM `business` WHERE `package` = 1 AND `city` = '$this_city' ORDER BY RAND()";
Since city is a string it should be wrapped in single quotes in the query.
It is included after
Posted: Tue Aug 08, 2006 10:47 am
by saltriver
The include does come after the vars are declared.
Posted: Tue Aug 08, 2006 11:45 am
by RobertGonzalez
Then before you execute the query, echo it out to see what the query is actually sending to database server. The only other explanation is that the var is being redeclared to empty before it is going to the query.
Posted: Tue Aug 08, 2006 2:41 pm
by saltriver
Since the city variable is still sitting on the index.php page, when I try to echo it out further down the page (after the include) it prints out.
When I try to echo it from the included page and preview that page in my browser, I get an empty page. If I put the vars in the page and preview that page, it prints out.
I just tried leaving the vars in the included page and previewing it the index.php page in my browser. I now have two versions of Part 2. One is just the SQL queries, the other is that AND the HTML and PHP that actually make up the meat of the page. LEaving the vars in these two isn't doing much other than just showing the little echo I put in there. The var isn't reconnecting with the PHP used in the index.php page and for whatever reason, the include page with the html isn't showing up at all, like it was breaking before it could get to any plain html.
Let me know if you need to see more extensive code for both pages.
Posted: Tue Aug 08, 2006 3:47 pm
by RobertGonzalez
If it is not a lot of code, post it here. If it is a lot of code, PM me. I can sift through it and post what is relevent.
Posted: Wed Aug 09, 2006 7:45 pm
by saltriver
I've stripped out some of the code that doesn't apply to the problem I'm having...
index.php:
Code: Select all
<?php
require_once('../../../Connections/dbname.php');
require_once('../../../Connections/dbnamestats.php');
mysql_select_db($database_dbname, $dbname);
$this_city = 'Concord';
$this_county = 'Essex';
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
</head>
<body>
<?php include("http://mywebsite.com/northern/includes/header.php");
echo $this_city; ?>
<?php include("http://mywebsite.com/northern/includes/leftnav.php");?>
<?php include("http://mywebsite.com/northern/includes/city_index.php"); ?>
<?php include("http://mywebsite.com/northern/includes/rightads.php");?>
<?php include("http://mywebsite.com/northern/includes/footer.php");?>
<?php
include("http://mywebsite.com/includes/imp_counter.php");
mysql_free_result($rsallsum);
mysql_free_result($rs_county);
mysql_free_result($rs_city);
mysql_free_result($rs_cuisine);
mysql_free_result($rs_menu_gold);
mysql_free_result($rs_rest);
mysql_free_result($rs_menu_silver);
mysql_free_result($rs_featured);
mysql_free_result($rs_co_city);
mysql_free_result($rs_ads);
?>
Here is the include I'm having the issues with...
city_index.php:
Code: Select all
<?php
require_once('../../Connections/dbname.php');
require_once('../../Connections/dbnamestats.php');
mysql_select_db($database_dbname, $dbname);
$query_rsallsum = "SELECT COUNT(status), rest_id FROM reviews WHERE status = 1 group by rest_id";
$rsallsum = mysql_query($query_rsallsum, $dbname) or die(mysql_error());
$row_rsallsum = mysql_fetch_array($rsallsum);
$totalRows_rsallsum = mysql_num_rows($rsallsum);
$query_rs_county = "SELECT * FROM locations WHERE loc_county = $this_county ORDER BY loc_city ASC";
$rs_county = mysql_query($query_rs_county, $dbname) or die(mysql_error());
$row_rs_county = mysql_fetch_assoc($rs_county);
$totalRows_rs_county = mysql_num_rows($rs_county);
$query_rs_menu_gold = "SELECT * FROM vt_restaurants WHERE package =3 AND vt_restaurants.city = $this_city ORDER BY RAND()";
$rs_menu_gold = mysql_query($query_rs_menu_gold, $dbname) or die(mysql_error());
$row_rs_menu_gold = mysql_fetch_assoc($rs_menu_gold);
$totalRows_rs_menu_gold = mysql_num_rows($rs_menu_gold);
$query_rs_rest = "SELECT * FROM vt_restaurants WHERE package=1 AND city = $this_city ORDER BY RAND()";
$rs_rest = mysql_query($query_rs_rest, $dbname) or die(mysql_error());
$row_rs_rest = mysql_fetch_assoc($rs_rest);
$totalRows_rs_rest = mysql_num_rows($rs_rest);
$query_rs_menu_silver = "SELECT * FROM vt_restaurants WHERE package = 2 AND vt_restaurants.city = $this_city ORDER BY RAND()";
$rs_menu_silver = mysql_query($query_rs_menu_silver, $dbname) or die(mysql_error());
$row_rs_menu_silver = mysql_fetch_assoc($rs_menu_silver);
$totalRows_rs_menu_silver = mysql_num_rows($rs_menu_silver);
$query_rs_rest = "SELECT * FROM vt_restaurants WHERE package=1 AND city = $this_city ORDER BY RAND()";
$rs_rest = mysql_query($query_rs_rest, $dbname) or die(mysql_error());
$row_rs_rest = mysql_fetch_assoc($rs_rest);
$totalRows_rs_rest = mysql_num_rows($rs_rest);
$adimp1id = $row_rs_ads['ads_id'];
$adimp1cid = $row_rs_ads['ads_clientid'];
include("../../../includes/imp_counter.php");
?>
<td width="460" align="left" valign="top"><DIV align=center>
<?php do { $arr[$row_rsallsum['rest_id']] = $row_rsallsum['COUNT(status)']; ?>
<?php } while ($row_rsallsum = mysql_fetch_array($rsallsum)); ?>
<table width="400" border="0" cellspacing="0" cellpadding="0">
<tr>
<td><div align="center" class="litemedblack">
<p><?php echo $this_city; ?> Vermont Restaurants</p></div>
</td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php do { ?>
<?php if ($totalRows_rs_menu_gold > 0) { // Show if recordset not empty ?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="0" class="medblack" id="goldtable">
<tr>
<td width="300" align="left" valign="top">
<span class="extramedblack"><a href="../../pop_up/details_northern.php?recordID=<?php echo $row_rs_menu_gold['id']; ?>"><?php echo $row_rs_menu_gold['restaurant']; ?></a> <a href="<?php echo $row_rs_menu_gold['website']; ?>" target="_blank"><?php echo $row_rs_menu_gold['webbutton']; ?></a></span>
<?php
if( !empty($arr[$row_rs_menu_gold['id']]) )
echo "<strong>".$arr[$row_rs_menu_gold['id']] . ' Review(s)</strong>';
?>
<br>
<span class="medblacknorm"><?php echo $row_rs_menu_gold['brief']; ?></span><br>
<span class="smallerblacknorm"><?php echo $row_rs_menu_gold['number']; ?> <?php echo $row_rs_menu_gold['street']; ?></span> -
<span class="smallblack"><?php echo $row_rs_menu_gold['phone']; ?></span><br>
<br>
</td>
<td width="10"> </td>
<td width="90" align="right" valign="top" class="smallerblack">
<?php echo $row_rs_menu_gold['cuisinea']; ?><br>
<?php echo $row_rs_menu_gold['cuisineb']; ?><br>
<?php echo $row_rs_menu_gold['cuisinec']; ?><br>
</td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php } while ($row_rs_menu_gold = mysql_fetch_assoc($rs_menu_gold)); ?>
<?php do { ?>
<?php if ($totalRows_rs_menu_silver > 0) { // Show if recordset not empty ?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="0" class="medblack" id="silvertable">
<tr>
<td width="300" align="left" valign="top">
<span class="litemedblack"><a href="../../pop_up/details_northern.php?recordID=<?php echo $row_rs_menu_silver['id']; ?>"><?php echo $row_rs_menu_silver['restaurant']; ?></a> <a href="<?php echo $row_rs_menu_silver['website']; ?>" target="_blank"><?php echo $row_rs_menu_silver['webbutton']; ?></a></span><br>
<?php
if( !empty($arr[$row_rs_menu_silver['id']]) )
echo "<strong>".$arr[$row_rs_menu_silver['id']] . ' Review(s)</strong>';
?>
<br>
<span class="medblacknorm"><?php echo $row_rs_menu_silver['brief']; ?></span><br>
<span class="smallerblacknorm"><?php echo $row_rs_menu_silver['number']; ?> <?php echo $row_rs_menu_silver['street']; ?></span> -
<span class="smallblack"><?php echo $row_rs_menu_silver['phone']; ?></span>
<br></td>
<td width="10"> </td>
<td width="90" align="right" valign="top" class="smallerblack">
<?php echo $row_rs_menu_silver['cuisinea']; ?><br>
<?php echo $row_rs_menu_silver['cuisineb']; ?><br>
<?php echo $row_rs_menu_silver['cuisinec']; ?><br>
</td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php } while ($row_rs_menu_silver = mysql_fetch_assoc($rs_menu_silver)); ?>
<?php do { ?>
<?php if ($totalRows_rs_rest > 0) { // Show if recordset not empty ?>
<table width="400" border="0" align="center" cellpadding="0" cellspacing="0" class="smallblack" id="listingstable">
<tr>
<td align="left" valign="top">
<a href="../../pop_up/details_northern.php?recordID=<?php echo $row_rs_rest['id']; ?>"><?php echo $row_rs_rest['restaurant']; ?></a> -
<span class="smallerblack">
<?php
if( !empty($arr[$row_rs_rest['id']]) )
echo "<strong>".$arr[$row_rs_rest['id']] . ' Review(s)</strong>';
?>
<br>
<?php echo $row_rs_rest['cuisinea']; ?> Cuisine</span><br>
<span class="smallblacknorm"><?php echo $row_rs_rest['brief']; ?><br>
<em><?php echo $row_rs_rest['number']; ?> <?php echo $row_rs_rest['street']; ?></em> - </span> <span class="smallgreen"><?php echo $row_rs_rest['phone']; ?></span>
<br><br></td>
</tr>
</table>
<?php } // Show if recordset not empty ?>
<?php } while ($row_rs_rest = mysql_fetch_assoc($rs_rest)); ?>
<br>
<?php include("http://mywebsite.com/includes/northern_counties.php"); ?>
<br>
</div>
</td>
Posted: Wed Aug 09, 2006 7:57 pm
by RobertGonzalez
Run this query in phpMyAdmin and post back what is returned. Please do not modify it. Run it exactly like this.
Code: Select all
SELECT * FROM locations WHERE loc_county = Essex ORDER BY loc_city ASC;
Posted: Wed Aug 09, 2006 9:47 pm
by saltriver
#1054 - Unknown column 'Essex' in 'where clause'
Doh!
I forgot the single quotes (again). Although I put them on in the city_index.php page when I realized that, and it still returns an empty query.
Posted: Wed Aug 09, 2006 11:54 pm
by RobertGonzalez
Almost every one of your queries are like that. I would seriously rewrite them all to make them clean. Also, I'd be really interested in why
mysql_error() is not working the way it should.