Page 1 of 3

taking 2 values with one query?

Posted: Wed May 14, 2003 9:03 am
by irealms
at present i have 2 queries in a script

//extract email address
$query = "select email from user where email='$mailpass'";
$result = mysql_query($query, $db_conn) or die('query failed');
//extract password
$query2 = "select passwd from user where email='$mailpass'";
$result2 = mysql_query($query2, $db_conn) or die('query failed');

then i've taken one into a variable

$pass = mysql_result($result2, 'passwd');

is it possible to combine the 2 queries and draw out what i want to make the variables?

Posted: Wed May 14, 2003 9:15 am
by volka

Code: Select all

$query = "select email,passwd from user where email='$mailpass'";

thanks

Posted: Wed May 14, 2003 9:26 am
by irealms
got that, how would i then call out 2 variables from the result ?

Posted: Wed May 14, 2003 10:03 am
by volka
e.g.

Code: Select all

$result = mysql_fetch_assoc($result);
then you'll have $result['email'] and $result['passwd'] (in case myqsl_... didn't fail)

k

Posted: Thu May 15, 2003 3:10 am
by irealms
i need users to have differing lvls of access and am thinking of making a 2nd table for this purpose. Would i need to make id in this too so that i can join the table with the user table?

Posted: Thu May 15, 2003 4:18 am
by Gleeb
Get into the habit of putting an 'AUTO_INCREMENT' ID into everything. It gives you a guarenteed uniqueness, so that you can have a much easier time of updating the right record :)

It also helps a great deal as part of a relational database, for things like join :)

)

Posted: Thu May 15, 2003 5:25 am
by irealms
i need to values into 2 tables from one statement how would i do that?

Posted: Thu May 15, 2003 6:49 am
by Gleeb
I don't quite get what you mean, but here are my guesses.

Q1) 'I need to put values into two tables in one statement'
A1) You can't, as far as I know

Q2) 'I need to get values from 2 tables in one statement'
A2) Try

Code: Select all

SELECT `Table1`.`SomeInfo`, `Table2`.`SomeOtherInfo` WHERE whatever = something;
Then once you've got your data into an associative(?) array, you can $array['SomeInfo'] or $array['SomeOtherInfo']

If the feild names conflict, you might want to chech how it's stored in the array using print_r()

)

Posted: Thu May 15, 2003 7:32 am
by irealms
thanks i'd better explain better as well, hehe

i have a user table and an access table when someone registers i need it to enter the user info and then enter values into access as well. As they both have auto id this will mean the user info matches with the access lvls.

Posted: Thu May 15, 2003 7:45 am
by Gleeb
From what you say, it seems like you're storing the access levels for a user in a different table from the other info about the user. Why not just use one table? You don't hae to worry about matching values then

If you still want to go down the 2 tables route, have a look at mysql_insert_id()

re volka's response

Posted: Thu May 15, 2003 7:47 am
by irealms
regarding volka's response about 2 queries do you mean like this?

<form method="post" action="index.php?log=forgot">
<input type="text" name="mailpass" style="style="font-size:10px;border:solid 1px;">
<input type=image src="buttons\send.gif" value="send" style="font-size:10px";>
</form>

<?php
if (!$_POST['mailpass'])
{
echo "<div class=\"log\">please enter your email address.</div>";
}
elseif (isset($_POST['mailpass']))
{
$mailpass = $_POST['mailpass'];
$db_conn = mysql_connect("localhost", "cadmin", "cpass") or die('cannot connect to db-server');
mysql_select_db("crimson", $db_conn) or die('cannot select database');

$query = "select email,passwd from user where email='$mailpass'";
$result = mysql_query($query, $db_conn) or die('query failed');
$result = mysql_fetch_assoc($result);


if (mysql_num_rows($result) >0 )
{

$email = $result['email'];
$from = "from: crimson@irealms.co.uk \r\n";
$mesg = "your password is $result['email']\r\n";
echo "<fieldset><legend>Mail sent to</legend> $email</fieldset>";
mail($email, $from, $mesg) or die('mail not sent');

}
else
echo 'record not found';
}
else
echo 'invalid request';
?>

Posted: Thu May 15, 2003 7:54 am
by Gleeb
That code will e-mail them their e-mail address. You're on the right track though.

Also...

Code: Select all

$query = "select email,passwd from user where email='$mailpass'"; 
$result = mysql_query($query, $db_conn) or die('query failed'); 
$result = mysql_fetch_assoc($result);
should be

Code: Select all

$query = "select email,passwd from user where email='$mailpass'"; 
$result = mysql_query($query, $db_conn) or die('query failed'); 
$row = mysql_fetch_assoc($result);
or something similar, so that you can get further rows from the result set. Not really applicable in this case, coz you only deal with one record, but a note to take for the futute :)

ok

Posted: Thu May 15, 2003 7:57 am
by irealms
so if i change that to row does row store the values and i use $row['email'] to get the email?

as in

$row = mysql_fetch_assoc($result);

then


$mesg = "your password is $row['passwd']\r\n";

i'm getting the following error in the mesg line

Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/httpd/vhosts/irealms.co.uk/httpdocs/crimson/forgot.php on line 31

Posted: Thu May 15, 2003 7:59 am
by Gleeb
Yup, and the password is in $row['passwd'] :)

ok still getting that error

Posted: Thu May 15, 2003 8:03 am
by irealms
Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in /home/httpd/vhosts/irealms.co.uk/httpdocs/crimson/forgot.php on line 31

the line in question is

$mesg = "your password is $row['passwd'] \r\n";