MySQL Question (Hope someone can answer)

ID:187382
 
Dec 21 2004, 11:01 pm
The following is an example of my MySQL (with important information changed)

$ID = $_POST['ID'];
$PW = $_POST['PW'];
$username = "theusername";
$password = "thepassword";
$database = "the_database";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database)or die("Cannot connect to database!");
$query = "SELECT * FROM accounts WHERE first = '$ID'";
$result = mysql_query($query);
mysql_close();

The only way I know how to access the correct account is to switch through all the rows using a loop until I go to the one I want, well with alot of accounts, I would imagine this taking too much time.

How can I go straight to a certain entree without having to seach with it using a loop? I figured $query = "SELECT * FROM accounts WHERE first = '$ID'"; had to do something with it but I cannot go further than what I've stated.

Thanks,
RiftHaven

Dec 22 2004, 12:01 am
your SELECT statement above will select *all* fields in a record that has the ID you are looking for, thereby picking the exact one you want, assuming ID is unique for all records. if not, just add another unique qualifier to the end of the select statement, such as ' AND second = '$PW' for example. that will improve your chance of making a single hit (unless ID and PW can be the same for more than one user, which is not a good idea anyway). $result should return an array with all the fields in the record that contains $ID in the first field (using your example).

it looks like what you are doing is ok at first glance. is it not giving you what you want?
Dec 22 2004, 9:13 am (Edited on Dec 22 2004, 10:41 am)
In response to digitalmouse
(message left blank)
Dec 22 2004, 10:43 am
In response to digitalmouse
It's still not working, here's the entire login code (again, important information is changed)


$ID = $_POST['ID'];
$PW = $_POST['PW'];
$loggedin = false;
$reason = "";
$isadmin = false;
$username = "***";
$password = "***";
$database = "***_***";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database)or die("Cannot connect to database!");
$query = "SELECT * FROM accounts WHERE first = '$ID'";
$result = mysql_query($query);
$num=mysql_numrows($result);
mysql_close();
$rownum=0;
if(isset($_COOKIE['LogInfo']) == 0)
{
if($ID != "")
{
if($ID == mysql_result($result,0,'ID'))
{
if($PW == mysql_result($result,0,'Password'))
{
$loggedin = true;
setcookie('LogInfo',$ID);
}
else
{
$reason = "Incorrect Password";
}
}
else
{
$reason = "Invalid ID";
}
}
}
else
{
$loggedin = true;
$ID = $_COOKIE['LogInfo'];
}




I keep getting my Incorrect ID, the database itself is correct because if I use a loop to go through all the accounts, then I can make a successful login code. However, I don't want to use a loop to go through every single one.

Thanks,
RiftHaven
Dec 23 2004, 6:07 pm
In response to Rifthaven
You don't need to call mysql_close(), and certainly not before you've extracted the information from the database. What's probably happening is that mysql_result() actually uses the MySQL connection to request that piece of data; MySQL is designed to be efficient for HUGE data sets, so it would be impractical to send the entire results of the query across the connection; particularly as MySQL is designed to operate over a network. So you're closing the connection when you haven't got all the information you need over it yet.

So just delete the call to mysql_close(). Don't worry about calling it later; PHP will close the connection for you when the script exits. It's generally quite well-behaved about things like that. (Even the manual entry for mysql_close() admits that it's usually unnecessary.)