Page 1 of 1

Getting data from a database problem

Posted: Fri Jun 12, 2009 1:19 pm
by hwmetzger
Hello everyone,
I'm pretty new at PHP but I have several years of programming experience. What I am trying to do seems like it should be really easy I just don't know where/how to start. This is for PHP and mySQL.

I want to have a page where the person logs in with a username and password (I have this part working already).

The program takes the username (once they are found to have an account) and it looks for a specific field in that user's row of the database table. Then it uses that value as a reference number to another table in the database.

Using that reference number, it take all the info in that row and passes it back to the program and performs several functions on it (The functions I have covered)

I probably explained that terribly. Any questions or help would be GREATLY appreciated!

Re: Getting data from a database problem

Posted: Fri Jun 12, 2009 1:33 pm
by akuji36
Once you have log in verified. I believe you need php sessions to take over.

follow this link:

http://learnola.com/2008/11/14/beginner ... -sessions/

Re: Getting data from a database problem

Posted: Fri Jun 12, 2009 1:36 pm
by requinix
Sessions?

Use a JOIN in your first look-up-the-user query. SELECT syntax | JOIN syntax

Code: Select all

SELECT user_table.FIELDS, other_table.FIELDS
    FROM user_table
    JOIN other_table ON user_table.foreign_key = other_table.primary_key
    WHERE conditions
If you get rows back (so the username/password matches) you'll get the other_table information too.

Note how if you do a SELECT * you'll get everything from all tables involved in the query - those in the FROM and those in the JOIN.
(But you probably want that to happen.)

Re: Getting data from a database problem

Posted: Fri Jun 12, 2009 1:48 pm
by hwmetzger
How would I go about setting the data from the database into variables?

Re: Getting data from a database problem

Posted: Fri Jun 12, 2009 3:13 pm
by requinix
Didn't you say you already had the login process working?

Re: Getting data from a database problem

Posted: Mon Jun 15, 2009 4:07 pm
by hwmetzger
Alright, I have run into a different problem

Everything is working but this:

Code: Select all

 
function get_store($value)
{
$sql = "SELECT storenumber FROM users WHERE uname LIKE '%".$value."%'";
$result = mysql_query($sql);
$item = mysql_fetch_assoc($results);
 
return $item;
}
 
The value that I get back from this function is "Resource id #5"

I have tried changing the field type in the database from a VARCHAR(32) to a TEXT(32) and back again. Get the same thing.

The value I am looking for would look like this: TX123

Re: Getting data from a database problem

Posted: Mon Jun 15, 2009 4:40 pm
by requinix

Code: Select all

$result = mysql_query($sql);
$item = mysql_fetch_assoc($results);
Look harder. Especially at the variables.

Re: Getting data from a database problem

Posted: Mon Jun 15, 2009 6:03 pm
by califdon
Also, mysql_fetch_assoc() returns one row from a query result. It is up to you to use the individual fields in that row, even when there's only one, as in your case.

Re: Getting data from a database problem

Posted: Tue Jun 16, 2009 6:50 am
by hwmetzger
So would I return something like this:

return $item[0];


or something?

Re: Getting data from a database problem

Posted: Tue Jun 16, 2009 7:00 am
by mattpointblank
Yep, or $row['columnName'].

Re: Getting data from a database problem

Posted: Tue Jun 16, 2009 10:00 am
by hwmetzger
Well, now I'm getting a different message.

Code: Select all

function get_store($value)
{
$sql = "SELECT storenumber FROM users WHERE uname =".$value."";
$result = mysql_query($sql);
 
$item = mysql_fetch_assoc($result); //This is line 41 in my program.
 
return $item[0];
}
Gets this:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home1/vancovis/public_html/test/report/functions.php on line 41

Re: Getting data from a database problem

Posted: Tue Jun 16, 2009 1:11 pm
by califdon
hwmetzger wrote:Well, now I'm getting a different message.

Code: Select all

function get_store($value)
{
$sql = "SELECT storenumber FROM users WHERE uname =".$value."";
$result = mysql_query($sql);
 
$item = mysql_fetch_assoc($result); //This is line 41 in my program.
 
return $item[0];
}
Gets this:
Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home1/vancovis/public_html/test/report/functions.php on line 41
That is telling you that your query did not produce a valid result. Always include an or die(mysql_error()) after a query, so that you will receive the error message when a query fails.

Code: Select all

$result = mysql_query($sql) or die(mysql_error());
(Actually, it appears to be referring to a different query... "mysql_fetch_row()"... ??)