taking 2 values with one query?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

taking 2 values with one query?

Post 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?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

$query = "select email,passwd from user where email='$mailpass'";
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

thanks

Post by irealms »

got that, how would i then call out 2 variables from the result ?
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post 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)
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

k

Post 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?
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post 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 :)
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

)

Post by irealms »

i need to values into 2 tables from one statement how would i do that?
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post 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()
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

)

Post 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.
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post 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()
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

re volka's response

Post 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';
?>
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post 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 :)
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

ok

Post 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
Last edited by irealms on Thu May 15, 2003 8:00 am, edited 1 time in total.
Gleeb
Forum Commoner
Posts: 87
Joined: Tue May 13, 2003 7:01 am
Location: UK
Contact:

Post by Gleeb »

Yup, and the password is in $row['passwd'] :)
User avatar
irealms
Forum Contributor
Posts: 215
Joined: Mon Apr 28, 2003 7:10 am
Location: Leeds

ok still getting that error

Post 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";
Post Reply