problem with getting info from 2 tables in PHP / MySQL

[Neal]

I've got 2 tables. One, I manually insert data into for HTTP
authentication. It has 3 fields: username, password, school. Once the
user enters the corrrect u and p, the school name shows up hard coded
into a form that gets submitted into another table. So, there is a
'school' field in both tables that always match. Now, I want to draw all
the info from this second table to print out but am having problems. So
far, I've got something like:

$sql = mysql_query("SELECT * FROM logintable, datatable WHERE
logintable.school = datatable.school");
while($row = mysql_fetch_array($sql)){

print("$row[school], $row[field1], etc....

}

[JDS]

On Tue, 02 Aug 2005 17:58:46 +0000, Neal wrote:

I've got 2 tables. One, I manually insert data into for HTTP

You lost me.

Post the results of

DESCRIBE <tablename>

for both tables. (i.e. what do the tables actually look like?)

Also, what are the problems you are having? All you say is "but am having
problems". Just not enough info, hoss.

--
JDS | je*****@example.invalid
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

[Neal]

JDS wrote:

You lost me.

Post the results of

DESCRIBE <tablename>

for both tables. (i.e. what do the tables actually look like?)

Also, what are the problems you are having? All you say is "but am having
problems". Just not enough info, hoss.

Sorry if I'm unclear. Basically, I'm giving unique passwords to schools
in our district so they can post jobs. This part works and all the info
gets put into a table called 'jobs'. But, I'm trying to make another
page where they can login and only their school will show up (so I can
later make it so they can delete their own entry if they fill their
position).

$sql = "SELECT * FROM login WHERE
username = '$PHP_AUTH_USER' AND
password = '$PHP_AUTH_PW'";
$result = mysql_query( $sql )
or die ( 'Unable to execute query.' );
$X = mysql_fetch_array($result);
$num = mysql_numrows( $result );
if ( $num != 0 ) {
$auth = true;
}
}

if ( ! $auth ) {

header( 'WWW-Authenticate: Basic realm="Private"' );
header( 'HTTP/1.0 401 Unauthorized' );
echo 'Authorization Required.';
exit;

} else {

$school = "$X[school]";
echo("$school");
$S1 = "SELECT * FROM jobs WHERE school = '$school'";
$S2 = mysql_query($S1);
while($S3 = mysql_fetch_array($S2))
{
print("$S3[title] : $S3[school]");
}

I put that echo part just to test if I get the correct school according
to the username and password (this part works). The problem I think is
with the WHERE statement because when I take that out, I get ALL the
entries from the jobs table printing the title field and the school
field like it should. But with the above WHERE statement where I'm
trying to limit it to one school only, I get just the school name from
the echo.

The login table has 3 fields: username, password, and school. The jobs
table has a bunch of fields, one of them being schools that I'm trying
to match to the login.
Sorry, if I'm not more clear.

[Scott Auge]

In article <ic*********************@fe03.news.easynews.com> ,
Neal <pi****@comcast.net> wrote:

JDS wrote:

You lost me.

Post the results of

DESCRIBE <tablename>

for both tables. (i.e. what do the tables actually look like?)

Also, what are the problems you are having? All you say is "but am having
problems". Just not enough info, hoss.

Sorry if I'm unclear. Basically, I'm giving unique passwords to schools
in our district so they can post jobs. This part works and all the info
gets put into a table called 'jobs'. But, I'm trying to make another
page where they can login and only their school will show up (so I can
later make it so they can delete their own entry if they fill their
position).

$sql = "SELECT * FROM login WHERE
username = '$PHP_AUTH_USER' AND
password = '$PHP_AUTH_PW'";
$result = mysql_query( $sql )
or die ( 'Unable to execute query.' );
$X = mysql_fetch_array($result);
$num = mysql_numrows( $result );
if ( $num != 0 ) {
$auth = true;
}
}

if ( ! $auth ) {

header( 'WWW-Authenticate: Basic realm="Private"' );
header( 'HTTP/1.0 401 Unauthorized' );
echo 'Authorization Required.';
exit;

} else {

$school = "$X[school]";
echo("$school");
$S1 = "SELECT * FROM jobs WHERE school = '$school'";
$S2 = mysql_query($S1);
while($S3 = mysql_fetch_array($S2))
{
print("$S3[title] : $S3[school]");
}

I put that echo part just to test if I get the correct school according
to the username and password (this part works). The problem I think is
with the WHERE statement because when I take that out, I get ALL the
entries from the jobs table printing the title field and the school
field like it should. But with the above WHERE statement where I'm
trying to limit it to one school only, I get just the school name from
the echo.

That is how it is suppose to work.

The login table has 3 fields: username, password, and school. The jobs
table has a bunch of fields, one of them being schools that I'm trying
to match to the login.
Sorry, if I'm not more clear.

It sounds like that is what it is doing.

Maybe you can describe what you were hoping to get.
--
Available for Hire! http://amduus.com/Resumes/

[Neal]

Scott Auge wrote:

In article <ic*********************@fe03.news.easynews.com> ,
Neal <pi****@comcast.net> wrote:

JDS wrote:

You lost me.

Post the results of

DESCRIBE <tablename>

for both tables. (i.e. what do the tables actually look like?)

Also, what are the problems you are having? All you say is "but am having
problems". Just not enough info, hoss.

Sorry if I'm unclear. Basically, I'm giving unique passwords to schools
in our district so they can post jobs. This part works and all the info
gets put into a table called 'jobs'. But, I'm trying to make another
page where they can login and only their school will show up (so I can
later make it so they can delete their own entry if they fill their
position).

$sql = "SELECT * FROM login WHERE
username = '$PHP_AUTH_USER' AND
password = '$PHP_AUTH_PW'";
$result = mysql_query( $sql )
or die ( 'Unable to execute query.' );
$X = mysql_fetch_array($result);
$num = mysql_numrows( $result );
if ( $num != 0 ) {
$auth = true;
}
}

if ( ! $auth ) {

header( 'WWW-Authenticate: Basic realm="Private"' );
header( 'HTTP/1.0 401 Unauthorized' );
echo 'Authorization Required.';
exit;

} else {

$school = "$X[school]";
echo("$school");
$S1 = "SELECT * FROM jobs WHERE school = '$school'";
$S2 = mysql_query($S1);
while($S3 = mysql_fetch_array($S2))
{
print("$S3[title] : $S3[school]");
}

I put that echo part just to test if I get the correct school according
to the username and password (this part works). The problem I think is
with the WHERE statement because when I take that out, I get ALL the
entries from the jobs table printing the title field and the school
field like it should. But with the above WHERE statement where I'm
trying to limit it to one school only, I get just the school name from
the echo.

That is how it is suppose to work.

The login table has 3 fields: username, password, and school. The jobs
table has a bunch of fields, one of them being schools that I'm trying
to match to the login.
Sorry, if I'm not more clear.

It sounds like that is what it is doing.

Maybe you can describe what you were hoping to get.

I'm hoping to retrieve and print records currently in the table where
the field 'school' = $school.
There are currently about 10 records in the table, and lets say one of
them has Washington HS in the
school field. When I put WHERE school = 'Washington HS'"; the
corresponding row (title : school)
will print out. But, when I put WHERE school = '$school'"; .... nothing.
Even though the echo("$school'); line prints out 'Washington HS' (if I
entered the username and password associated with this school from the
login table).

Basically, this is an attempt at session management using 2 MySQL tables
so when the user logs (using the login table), they'll only see their
records from another table I'm trying to associate with the login table.
If there is
another way....

[Sergej Andrejev]

Recently I had a cource in university about DBMS and I think you should
use 3 tables rather than 2

TABLE login(user_id, username, password, school_id)

then TABLE shools(school_id, school_name, other_info)

and finaly TABLE shools(job_id, school_id, other_info)

Now you can login by selecting user from login table like this
SELECT * FROM login WHERE username = '$username' AND password =
'$password';
when you are logen you have wto id variables user_id(to track user) and
school_id (to link user with some school)

To limit your select to one school try
SELECT * FROM jobs WHERE user_id = '$user_id' AND school_id =
'school_id';
on the other hand if one user can mantain more than one school you can
replace AND to OR and get
SELECT * FROM jobs WHERE user_id = '$user_id' OR school_id =
'school_id';

Good luck

[JDS]

On Tue, 02 Aug 2005 23:30:52 +0000, Neal wrote:

will print out. But, when I put WHERE school = '$school'"; .... nothing.
Even though the echo("$school'); line prints out 'Washington HS' (if I
entered the username and password associated with this school from the
login table).

I'm just guessing. Not enough time at the moment to get too in-depth.

Does the 'school' column EXACTLY match the value "Washington HS"? If not,
you will get no matches.

It is a Really Bad Idea(TM) to use a string value column to do joins on,
expecially if the string values were all entered by hand. It is much much
better to use an Integer value.

How do you get an integer value for a string? In a RDBMS, you put the
strings in *another* table, in this case, a table called "schools" or some
such.

Then you will do a multi-table join.

I realize that putting all the schools in a third table sounds like extra
work and extra complication, but it will make for a more flexible,
scalable, and programmable database in the long run.

Google a bit on "database normalization"

later...

--
JDS | je*****@go.away.com
| http://www.newtnotes.com
DJMBS | http://newtnotes.com/doctor-jeff-master-brainsurgeon/

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[utque]

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

[Shelly]

"utque" <ut*****@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

while you are accessing the sql database, try using the INNER JOIN
command.
$sql = mysql_query("SELECT * FROM logintableb INNER JOIN datatable
WHERE
logintable.school = datatable.school");

I am not sure of it but trying doesn't hurt.

You post appeared **six** times. There seems to be a problem with your
newsreader.

This works:

$sql = mysql_query("SELECT * FROM logintableb, datatable WHERE
logintable.school = datatable.school");

there is no need to explicitly state "INNER JOIN".

Shelly