473,245 Members | 1,553 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,245 software developers and data experts.

problem with getting info from 2 tables in PHP / MySQL

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

}
Aug 2 '05 #1
13 1958
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/

Aug 2 '05 #2
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.
Aug 2 '05 #3
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/
Aug 2 '05 #4


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

Aug 2 '05 #5
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

Aug 2 '05 #6
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/

Aug 3 '05 #7
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.

Aug 3 '05 #8
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.

Aug 3 '05 #9
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.

Aug 3 '05 #10
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.

Aug 3 '05 #11
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.

Aug 3 '05 #12
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.

Aug 3 '05 #13

"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
Aug 3 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Patrick Crowley | last post by:
I'm creating a tool to browse a database of movie listings. The browser pulls up 25 results at a time, and you can page through them using 'Next' and 'Prev' tools. Pretty basic stuff. Here are...
0
by: I.P. | last post by:
------=_NextPart_000_03FF_01C368A4.75720DC0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable Hi, it's my story. I have two 4.0.14 mysql server on...
0
by: Gary Cote | last post by:
>Description: I've recently compiled mysql 4.0.14 on redhat 8.0. The installation fails, however, when running mysql_install_db. I've attached a transcript below. I gather from searching the...
3
by: Vanchau Nguyen | last post by:
Hi all, Hoping someone can help us with this strange Innodb problem. At about 5:15 AM 2/2/04 (this morning), our slave server stopped accepting connections. The machine was pingable, but you...
1
by: erikcw | last post by:
Hi, I'm trying to insert some data from an XML file into MySQL. However, while importing one of the files, I got this error: Traceback (most recent call last): File "wa.py", line 304, in ?...
3
by: h4xPace | last post by:
I am building a MySQL query application, and I have run into a small snag. MySQL has released a set of classes that extend the .NET framework base data classes (command, connection, etc), and I am...
10
by: marting | last post by:
Before I throw my new expensive laptop out of the window! I'm stuck on getting my joins right with the correct amount in a column count. After speaking with someone a few weeks back, they...
2
by: panos100m | last post by:
Hi I am using DB_dataobjects and pear . I noticed that for some of my tables my insert statement doesnt work correclty .. Those tableas are tables that i changed recently (i removed the space...
2
by: amit2781 | last post by:
Hi, I have created 4 tables in 'amit' database and then I deleted them. Still I able to get information about the table_schema for the table deleted. After drop table when I fire a query for...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.