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

PHP include for a common query

Post 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:

Code: Select all

$thiscity = 'Springfield';
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?
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

show us the content of your include file please
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

It is included after

Post by saltriver »

The include does come after the vars are declared.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

Post 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']; ?>&nbsp;<?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">&nbsp;</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']; ?>&nbsp;<?php echo $row_rs_menu_silver['street']; ?></span> - 
					  <span class="smallblack"><?php echo $row_rs_menu_silver['phone']; ?></span>
                      <br></td>
                  <td width="10">&nbsp;</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>&nbsp;&nbsp;-&nbsp;&nbsp;
				  <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']; ?>&nbsp;<?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>
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

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