Need help with runaway database queries!

Dave_5

I'm a relative newbie trying to make my first database-driven website. As a result of all my experiments, I now have three separate queries, as follows:

$query = " SELECT IDArea, Name, NNGW, QGW, SigGW
FROM basics
WHERE IDArea LIKE '$mycode'";
$data = array();
$res = mysql_query($query);
if ($res == mysql_query($query)) { while ($temp = mysql_fetch_assoc($res)) { $data[] = $temp;} }
if (mysql_errno()){ echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br />When executing:<br />\n$query\n<br />";
    exit; }
while ($temp = mysql_fetch_assoc($res)){ $data[] = $temp; }

$qry = "SELECT a.Name, a.AreaType FROM basics as b, area as a WHERE a.IDArea = b.IDArea and b.IDArea = '$mycode'";
$result = mysql_query($qry);
$type = mysql_fetch_array($result);
$mytype = "my" . $type['AreaType'];
$$mytype = $type['Name'];

$query = " SELECT IDArea, Parent1, Parent2
FROM parent
WHERE IDArea LIKE '$mycode'";
$data = array();
$res = mysql_query($query);
if (mysql_errno()){ echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br />When executing:<br />\n$query\n<br />";
    exit; }
while ($temp = mysql_fetch_assoc($res)){ $data[] = $temp; }

I couldn't delete any single query without screwing something up. In particular, the last query - connecting with the table named "parents" - knocks some functions out of commision, yet if I remove it, other items don't work.

So I tried to figure out how to combine the queries into a single query, and I wound up with this...

SELECT area.*, basics.*, parent.* FROM area, basics, parent WHERE
  (
   (parent.IDArea = area.IDArea) AND
   (area.IDArea = basics.IDArea) AND
   (basics.IDArea = '$mycode')
   )

but I got a parse error, so I changed it to this...

$query = "SELECT area.*, basics.*, parent.* FROM area, basics, parent WHERE
  (
   (parent.IDArea = area.IDArea) AND
   (area.IDArea = basics.IDArea) AND
   (basics.IDArea = '$mycode')
   )";

Now I get lots of errors similar to these:

Warning: Invalid argument supplied for foreach()

Warning: reset(): Passed variable is not an array or object in C:\sites\edrevolt\world\eur\deu\index.php on line 75

The obvious reason is that my new query omits some of the special functions that were built into the queries it replaced. So I'd like to know how to add them to my new query.

To put everything into perspective, let me offer a brief overview of what I'm trying to accomplish.

Using includes, database variables and echo functions, every page on my site will be absolutely identical except for this:

$mycode = '[something or other]';

For example, one page might be

$mycode = 'usa'; and another
$mycode = 'mex';.

These two pages represent the United States and Mexico. "usa" and "mex" are values that are also found in a field named IDArea on several tables, including "area," "parent" and "basics."

If a page is identified as "mex" (Mexico), the name "Mexico" will be drawn from the table "area," the Capital (Mexico City) from the table "basics" and Mexico's "parent" (North America) from the table "area."

I've been working on this for weeks, studying tutorials and asking questions on forums. I've been advised to use many different strategies, none of which I fully understand. However, I got it to the point where virtually everything was working.

I THINK all I have to do is refine my query, before proceeding to the final obstacle - incorporating a "parent-sibling" relationship into the whole thing.

I also posted my source code for the entire page below.

I suspect most pros will find the solution much simpler than my explanation. 🙂

<?php error_reporting(E_ALL);
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<?php
$mycode = 'deu';

[DATABASE CONNECTION]

// DELETED QUERIES...
/*
$query = " SELECT IDArea, Name, NNGW, QGW, SigGW
FROM basics
WHERE IDArea LIKE '$mycode'";
$data = array();
$res = mysql_query($query);
if ($res == mysql_query($query)) { while ($temp = mysql_fetch_assoc($res)) { $data[] = $temp;} }
if (mysql_errno()){ echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br />When executing:<br />\n$query\n<br />";
    exit; }
while ($temp = mysql_fetch_assoc($res)){ $data[] = $temp; }

$qry = "SELECT a.Name, a.AreaType FROM basics as b, area as a WHERE a.IDArea = b.IDArea and b.IDArea = '$mycode'";
$result = mysql_query($qry);
$type = mysql_fetch_array($result);
$mytype = "my" . $type['AreaType'];
$$mytype = $type['Name'];

$query = " SELECT IDArea, Parent1, Parent2
FROM parent
WHERE IDArea LIKE '$mycode'";
$data = array();
$res = mysql_query($query);
if (mysql_errno()){ echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br />When executing:<br />\n$query\n<br />";
    exit; }
while ($temp = mysql_fetch_assoc($res)){ $data[] = $temp; }

*/


$query = "SELECT area.*, basics.*, parent.* FROM area, basics, parent WHERE
  (
   (parent.IDArea = area.IDArea) AND
   (area.IDArea = basics.IDArea) AND
   (basics.IDArea = '$mycode')
   )";

$myname = ''; foreach ($data as $r){ $myname .= $r['IDArea']; }; // 'Name' didn't work.
$mykon = ''; foreach ($data as $r){ $mykon .= $r['IDArea']; }; // 'Name' didn't work.
$kon = ''; foreach ($data as $r){ $kon .= $r['Parent1']; }; // Renamed body from eur to ceu to make this work.
$mynat = ''; foreach ($data as $r){ $mynat .= $r['Parent1']; };
$nat = ''; foreach ($data as $r){ $nat .= $r['Parent1']; };
$mysta = ''; foreach ($data as $r){ $mysta .= $r['Parent1']; };
$sta = ''; foreach ($data as $r){ $myname .= $r['Parent1']; };
$nickname = ''; foreach ($data as $r){ $nickname .= $r['IDArea']; }; // 'NNGW' didn't work.

<head>
<?PHP
include ($_SERVER['DOCUMENT_ROOT']."/a1/inc/head/world.php");
include ($_SERVER['DOCUMENT_ROOT']."/a1/inc/head/$kon.php");
include ($_SERVER['DOCUMENT_ROOT']."/a1/inc/head.php");

</head>

<h1 class="title" id="title<?=$mycode"><?php echo "$myname" </h1>
<div class="divtop">
  <br />
<div class="subtitle" id="sub<?=$mycode"><span class="subspan">
<?php foreach ($data as $r){ print $r['NNGW']; }
</span></div>
<div class="quotewrap" id="qwrap<?=$mycode">
<div class="quote" id="q<?=$mycode">
<?php reset($data); while ($r = each($data)){ print $r[1]['QGW']; } </div>
<div class="sig" id="sig<?=$mycode">
<?php reset($data); while ($r = each($data)){ print $r[1]['SigGW']; } </div>
</div><!-- EndQuoteWrap -->
<div class="toplinks" id="toplinks<?=$mycode"><a href="/index.php">EdRevolt Home</a> &gt;
<?=$sectionlink<?php echo "$continentlink" <?php echo "$nationlink"
<?php echo "$statelink" <?php echo "$myname" </div>
</div><!-- EndDivTop -->

<body class="<?php echo "$type" " id="<?php echo "$mycode" " onload="P7_swapClass(0,'none','showIt','hideIt','div');typeWriter('quote',100)">
<?php include ($_SERVER['DOCUMENT_ROOT']."/a1/inc/body.php");
</body>
</html>

bubblenut

I need to know your database structure to really be of much help. A couple of points though. You are needlessly looping over the same data over and over again in many places. This is a waste of processing and should be fixed. I have done a little bit of work on your code and left a couple of comments but I really need the database structure to go any further.

<?php
//Join the two queries giving specific names where clashes occur
$query ="SELECT basics.IDArea as IDArea, ".
			"basics.Name as BasicsName, ".
			"basics.NNGW as NNGW, ".
			"basics.SigGW as SigGW, ".
			"area.Name as AreaName, ".
			"area.AreaType as AreaType ".
		"FROM basics, area ".
		"WHERE basics.IDArea=area.IDArea ".
		"&& basics.IDArea LIKE '$mycode'";
$data = array();
//The or die part means that if the function returns false the program will output the given error and halt
$res=mysql_query($query) or die("Query: ".$query."<br />\nError: ".mysql_error());

while($temp = mysql_fetch_assoc($res)) {
	//You should really try to move some of the processing into this
	//loop, you may not need to run through the loop more than once
	$data[]=$temp;
}
//if we have found any results
if(count($data)>0) {
	$mytype = 'my'.$data[0]['AreaType'];
	$$mytype = $data[0]['AreaName'];
}

//I can't optimize this further without knowing your database structure.
//If parent to basics is a one to many or a many to many relationship
//there may be a little bit of trouble, on the other hand if it's a 
//many to one or one to one relationship we can join it in to the above 
//query without much difficulty
$query = " SELECT IDArea, Parent1, Parent2
FROM parent
WHERE IDArea LIKE '$mycode'";
$data = array();
$res = mysql_query($query);
if (mysql_errno()){ echo "MySQL error ".mysql_errno().": ".mysql_error()."\n<br />When executing:<br />\n$query\n<br />";
    exit; }
while ($temp = mysql_fetch_assoc($res)){ $data[] = $temp; }
?>

Dave_5

Thanks. I put a screenshot of two of my database tables online at http://www.geoworld.org/areaparent.gif last night. Note that I've since renamed the second table from parent to "family."

Also, note that the fields furthest to the right - NA and NP - serve no real purpose other than letting me know how many rows there are and helping me group things in a certain order.

In the table "area," the field AreaType is filled with abbreviations for words like "nation," "continent" and "state."

Basically, this table simply names a wide variety of nations, states, etc., assigning each one an identification code and classifying it as one of several basic "types."

The second table - now named "family" - defines the relationship between these jurisdictions. Unfortunately, I chose some poor examples to put online - like the Arctic Ocean!

Let's use Alaska as an example instead:

IDArea Parent1

us-ak | United States
United States | North America

Don't worry about the Parent2 field. That's experimental; I want to use it to for special cases. For example, I've assigned Costa Rica to North America (Parent1), but I may also classify it as "Central America" (Parent2). Or I may classify Russia and Turkey as belonging to both Europe (Parent1) and Asia (Parent2).

The table "basics" simply adds a variety of values, such as Nicknames, Capital and Population.

My ultimate objective is to simply make functions like these:

$myname = ''; foreach ($data as $r) { print $r['IDArea']; };
$myname = ''; foreach ($data as $r){ $myname .= $r['IDArea']; }; // 'Name' didn't work.
$mykon = ''; foreach ($data as $r){ $mykon .= $r['IDArea']; }; // 'Name' didn't work.
$kon = ''; foreach ($data as $r){ $kon .= $r['Parent1']; }; // Renamed body from eur to ceu to make this work.
$mynat = ''; foreach ($data as $r){ $mynat .= $r['Parent1']; };
$nat = ''; foreach ($data as $r){ $nat .= $r['Parent1']; };
$mysta = ''; foreach ($data as $r){ $mysta .= $r['Parent1']; };
$sta = ''; foreach ($data as $r){ $myname .= $r['Parent1']; };
$nickname = ''; foreach ($data as $r){ $nickname .= $r['IDArea']; }; // 'NNGW' didn't work.

display values from my database. However, these foreach functions don't have to look just like that. Maybe I need to change them as well.

I'm just in over my head dealing with so many things I don't really understand. But I think the solution is relatively simple.

Thanks!

Dave_5

It might also help if I go back to square one once again.

Consider the page below:

<head>
$mycode = 'deu';
$myname = 'Germany';
$mycapital = "Berlin'
</head>
<body>
<?php echo "$mycapital" ?>
</body>

All I want to do is plug this into three database tables named "family," "area" and "basics," then replace every echo function except $mycode with a function that is connected to the database:

<head>
$mycode = 'deu';
$myname = '[Database]';
$mycapital = "[Database]'
</head>
<body>
<?php echo "$mycapital" ?>
</body>

I just need to know how to write the query and what to substitute for the echo functions.