Page 1 of 2

Need help beating my query into submission...

Posted: Sat Jun 19, 2004 3:38 pm
by David B
Please don't read this unless you're a patient person! I'm a newbie who's trying to make a database-driven website, and I don't fully understand the nature or purpose of many of the functions I'm playing with. I seem to be moving in the right direction - one painful lurch at a time.

Anyway, I wound up with three separate queries, as follows:

Code: Select all

$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. I THINK this may be a step in the right direction...

Code: Select all

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

Code: Select all

$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, followed by a copy of my source code (for the entire page).

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

$mycode = 'usa';

OR

$mycode = 'mex';, etc.

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 week, 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 suspect most pros will find the solution much simpler than my explanation. :)

Code: Select all

<?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">
&nbsp; <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> > 
<?=$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>

Posted: Sat Jun 19, 2004 4:48 pm
by McGruff
Way too much code to read through it all... but for the query try an INNER JOIN:

Code: Select all

"SELECT area.colA, basics.colB &#1111;, ..etc..] FROM parent
INNER JOIN area USING(IDarea)
INNER JOIN basics USING(IDarea)
WHERE basics.IDarea = "' . $mycode . '"

Posted: Sat Jun 19, 2004 5:21 pm
by David B
I'm just a beginner at joins, too. The code you listed gives me a parse error. Also, I have to add $query = to the beginning, right?

Code: Select all

$query = "SELECT IDArea, Parent1 [, ..etc..] FROM family
INNER JOIN area USING(IDArea)
INNER JOIN basics USING(IDArea)
WHERE basics.IDarea = "' . $mycode . '"
You listed area.colA and basics.colB in the first line. That designates fields named colA and colB from tables named area and basics, respectively, right? If so, I'm confused, because the first line is deriving fields FROM "parent." (Incidentally, I just renamed parent to "family" because I read that "parent" may be "reserved" by MySQL, or something like that.)

Code: Select all

"SELECT area.colA, basics.colB [, ..etc..] FROM parent
INNER JOIN area USING(IDarea)
INNER JOIN basics USING(IDarea)
WHERE basics.IDarea = "' . $mycode . '"
The other lines make sense, but I'm confused by the first line. Also, I'm not quite sure what the entire query would look like.

Thanks.

Posted: Sat Jun 19, 2004 6:42 pm
by d3ad1ysp0rk
delete the [, ..etc..] unless you're adding more columns/tables.

Posted: Sat Jun 19, 2004 6:55 pm
by David B
LiLpunkSkateR wrote:delete the [, ..etc..] unless you're adding more columns/tables.
Yes, I already deleted that. Thanks.

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. I've tried many different strategies, some of which seemed to do the job, but I've never been able to get any one of them completely squared away.

Posted: Sat Jun 19, 2004 7:23 pm
by McGruff
David B wrote:I'm just a beginner at joins, too.
This article is essential reading: http://www.oreilly.com/catalog/javadtab ... r/ch02.pdf

Also see the mysql manual (mysql.com).
The code you listed gives me a parse error. Also, I have to add $query = to the beginning, right?
Correct.

It ought to work. The $query declaration in your post is not terminated with a semi-colon: is that causing the parse error?
You listed area.colA and basics.colB in the first line. That designates fields named colA and colB from tables named area and basics, respectively, right?
Correct. Name all the cols you require.
If so, I'm confused, because the first line is deriving fields FROM "parent."
It's actually selecting fields (named in the SELECT clause) from ALL the tables linked with INNER JOIN.
(Incidentally, I just renamed parent to "family" because I read that "parent" may be "reserved" by MySQL, or something like that.)
I don't remember offhand if that would create a parse error - try it again with the new table name.

Posted: Sat Jun 19, 2004 7:52 pm
by David B
Hmmmm... here's my query...

Code: Select all

$query = "SELECT IDArea, Parent1 FROM family
INNER JOIN area USING(IDArea)
INNER JOIN basics USING(IDArea)
WHERE basics.IDarea = "' . $mycode . '";
and this is the error message I'm getting:

"Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING in C:\sites\edrevolt\world\eur\deu\index.php on line 122"

The other thing that confuses me is displaying the data. If I get this to working, and I want to replace "Spain" in the following echo function with a database variable, what would it look like?

$mynat = 'Spain";

$mynat = '???';

Thanks.

Posted: Sat Jun 19, 2004 8:20 pm
by feyd
you've mixed up the order in which the single and double quotes need to be:

Code: Select all

$query = "SELECT IDArea, Parent1 FROM family 
INNER JOIN area USING(IDArea) 
INNER JOIN basics USING(IDArea) 
WHERE basics.IDarea = '" . $mycode . "'";

Posted: Sat Jun 19, 2004 8:35 pm
by David B
Thanks. I posted your revision, and I don't see any query-related errors, so it seems to work. Thanks!

It would appear that all the errors I see are related to my display code.

Items like this:

$mysta = ''; foreach ($data as $r){ $mysta .= $r['Parent1']; };

appear to be responsible for error messages like this:

Invalid argument supplied for foreach() in C:\sites\edrevolt\world\eur\deu\index.php on line 135

And functions like this:

<?php reset($data); while ($r = each($data)){ print $r[1]['QGW']; } ?>

appear to be the cause of error messages like this:

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

So assuming that this query is actually working...

Code: Select all

$query = "SELECT IDArea, Parent1 FROM family
INNER JOIN area USING(IDArea)
INNER JOIN basics USING(IDArea)
WHERE basics.IDarea = '" . $mycode . "'";
can someone show me how to display database data using echo functions and in the body? For example, if I want to replace "Japan" with a database variable in the following example, what would the finished product look like?

<?php
$myname = 'Japan';
?>

And if I want to link a variable in the body section to the database, what I replace <?php reset($data); while ($r = each($data)){ print $r[1]['QGW']; } ?> with?

I'm hopelessly confused by all this foreach, reset, print, display, echo stuff!

Thanks.

Posted: Sat Jun 19, 2004 8:42 pm
by feyd
It's kind of hard to tell without the revised code to stare at, could you post/update it?

Posted: Sat Jun 19, 2004 9:23 pm
by David B
Sure. I hope it isn't too big a mess; I deleted most of the insignificant stuff.

Thanks!

Code: Select all

<?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]
$query = "SELECT IDArea, Parent1 FROM family
INNER JOIN area USING(IDArea)
INNER JOIN basics USING(IDArea)
WHERE basics.IDarea = '" . $mycode . "'";

$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.
?>

<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">
&nbsp; <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> > 
<?=$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>

Posted: Sat Jun 19, 2004 10:04 pm
by feyd
well. $data isn't set, ever. you don't perform a query, just set a string to a SQL string..

Posted: Sat Jun 19, 2004 10:11 pm
by David B
feyd wrote:well. $data isn't set, ever. you don't perform a query, just set a string to a SQL string..
I don't understand what you mean - delete $data?

If I change this...

$mynat = ''; foreach ($data as $r){ $mynat .= $r['Parent1']; };

to this...

$mynat = ''; foreach { $mynat .= $r['Parent1']; };

I get a parsse error. This doesn't work:

$mynat = ''; foreach print{ $mynat .= $r['Parent1']; };

How do I write it?

Posted: Sat Jun 19, 2004 10:33 pm
by feyd
your original code has a commented section, this section created the variable $data (as an array) which you are trying to access now. You may want to mangle that into your code..

Posted: Sat Jun 19, 2004 10:40 pm
by David B
feyd wrote:your original code has a commented section, this section created the variable $data (as an array) which you are trying to access now. You may want to mangle that into your code..
I don't know how to do that. I've been trying to find someone who knows how to rewrite my table join with the array function for more than a day now without success. So I figured the other option would be to just scrap the array and change the display functions.

Are you saying the display functions CAN'T be changed - I'm stuck with the foreach/reset functions?