[SOLVED] PHP Newbie - Trying to limit selections

[Sydcomebak]

You arrived at this page from a link where you selected "B" meaning that you want to list all residents whose last name starts with "B"

 

Assume the following structure:

 

PeopleTbl

-NameID (unique)

-NameLast

-NameFirst

 

ResidentTbl

-ResidentID (unique)

-PeopleID

-HouseID

 

HousesTbl

-HouseID (unique)

-Address

 

The following variables are brought in from a link:

-$upbound = B

-$lowbound = C

 

All residents are people, but not all people are residents.

 

I want to list all of the last names, first names of the people whose PeopleID is found in the ResidentTbl under PeopleID (and their name is between $upbound and $lowbound variables).

 

The HouseIDs for all residents that are listed will be passed along through the names being clickable.

 

All help is appreciated!

 

-Dave

 

[ratcateme]

you could get a list of ID numbers from the ResidentTbl

then put each one into this query

$query = "SELECT * FROM `PeopleTbl` WHERE `NameLast` = '$upbound%' AND `ID` = '{$id}';";

there is also properly a way to get mysql to do the second query when it is selecting the ID's from ResidentTbl but i don't know how

 

Scott.

[Sydcomebak]

OK, maybe I should have said "Super-Newbie" in the title... =)

 

I hadn't thought of going to the PeopleTbl first, but that certainly makes sense.. the query I have now is:

<?php

// Request the text of all the names
$result = mysql_query("SELECT NameLast, NameFirst FROM PeopleTbl");
if (!$result) {
echo("<P>Error performing query: " .
mysql_error() . "</P>");
exit();
}
// Display the text of each name in a paragraph
while ( $row = mysql_fetch_array($result) ) {
// get only the names we want
if ($row["NameLast"]<$upbound) {

if ($row["NameLast"]>$lowbound) {

echo("<P>" . $row["NameLast"] . ", "  . $row["NameFirst"] . "</P>");
} } } ?>

 

But this was a test query where I was finding <i>people</i> and not <i>residents</i>. I now want to limit the people displayed to the ones whose ID is also present in the ResidentsTbl.

 

Hmm, the gears are turning...

[libertyct]

SELECT NameLast, NameFirst 
FROM tblPeople 
INNER JOIN tblResident ON (tblPeople.PeopleID = tblResident.PeopleID)
WHERE SUBSTRING(NameLast,1,1) = $variableB

 

key is the INNER JOIN

[Sydcomebak]

SELECT NameLast, NameFirst 
FROM tblPeople 
INNER JOIN tblResident ON (tblPeople.PeopleID = tblResident.PeopleID)
WHERE SUBSTRING(NameLast,1,1) = $variableB

 

key is the INNER JOIN

 

That looks /too/ simple. *smacks forehead* OK, let me try and intertwine that with the upper and lower bounds and see what happens. Thanks so much!

 

Edit: Ah, looks like you even took care of the upper and lower bound for me by checking the string within the NameLast... Well played...

[Sydcomebak]

I renamed all the variables to match my tables.  I also went ahead and changed the page that links to this one to pass only 1 variable.

 

<?php

SELECT NameLast, NameFirst 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.PeopleID = ResidentTbl.PeopleID)
//50
WHERE SUBSTRING(NameLast,1,1) = $var
echo("<P>" . $row["NameLast"] . ", "  . $row["NameFirst"] . "</P>");

?> 

 

Why do I feel like I am missing something like:

 

$result = mysql_query(

 

and a

 

while ( $row = mysql_fetch_array($result) ) {

 

?

 

I'm trying to pick up this stuff as I go along. 

[libertyct]

oh lol ok

 

$result = mysql_query("SELECT NameLast, NameFirst 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.PeopleID = ResidentTbl.PeopleID)
WHERE SUBSTRING(NameLast,1,1) = $var");

while ( $row = mysql_fetch_array($result) ) 
{
       echo $row['NameLast']." ".$row['NameFirst'];
       echo "<br>";
}

 

this prints something like:

 

Smith John

Adams Tom

Chisa Joan

 

etc....

[Sydcomebak]

<b>mysql_fetch_array(): supplied argument is not a valid MySQL result resource in ...</b>

 

Hmm, I'm double and tripple checking the variable names and nothing seems wrong.

[discomatt]

This means your mysql_query returned FALSE. Use mysql_error() to find out what went wrong in your query.

[Sydcomebak]

I renamed a couple things and thought that this would fix things, but I'm getting the same error.

 

<?php
$result = mysql_query("SELECT NameLast, NameFirst, NameID 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.NameID = ResidentTbl.NameID)
WHERE SUBSTRING(NameLast,1,1) = $var");

while ($row = mysql_fetch_array($result) ) 

{
       echo $row['NameLast']." ".$row['NameFirst'];
       echo "<br>";
}
?> 

[Sydcomebak]

OK, When I ran the script through MySQL, It told me that the columns were ambiguous.  I changed the name of a column, and it went through cleanly.  My data showed up just fine.

 

<?php
$result = mysql_query("SELECT * 
FROM PeopleTbl 
INNER JOIN ResidentTbl ON (PeopleTbl.NameID = ResidentTbl.Name_ID)
WHERE SUBSTRING(NameLast,1,1) = $var");
?>

 

Unfortunately, as soon as I go back to the HTML, it still gives me an error:

 

<b>mysql_fetch_array(): supplied argument is not a valid MySQL result resource in </b>...<b>on line 27</b>

 

Line 27 starts the fetch that follows:

 

<?php
while ($row = mysql_fetch_array($result) ) 

{
       echo $row['NameLast']." ".$row['NameFirst'];
       echo "<br>";
}
?>

 

Am I missing something?