Page 1 of 1
MySQL Query Array Help
Posted: Wed Mar 07, 2007 6:46 pm
by ianhull
Hi Guys,
I need to create a mysql query which will get the data into an array for me in the following format
Code: Select all
$ checkUser = mysql_query("SELECT * FROM users WHERE username = '$username' and password = '$password'");
I need to output
Code: Select all
while ($userChecked = mysql_fetch_array($checkUser)){
extract($userChecked);
//Instead of extracting all the users details I would like to setup session data
// $_SESSION['userDetails'][COLUMNNAME_FROM_DB_OR_NUMBER] = value1stcolumn;
// $_SESSION['userDetails'][COLUMNNAME_FROM_DB_OR_NUMBER] = value2ndcolumn;
If anyone can help with this it would be great
My overall aim is the be able to access this data like this
Code: Select all
$userid = $_SESSION['userDetails'][0];
$firstname = $_SESSION['userDetails'][1];
or like this
$userid = $_SESSION['userDetails'][id];
$firstname = $_SESSION['userDetails'][username];
you get the idea?
Posted: Wed Mar 07, 2007 7:06 pm
by RobertGonzalez
Code: Select all
<?php
$checkUser = mysql_query("SELECT * FROM users WHERE username = '$username' and password = '$password'") or die(mysql_error());
while ($row = mysql_fetch_array($checkUser)
{
// Everything you want is in the $row array now
$_SESSION['username'] = $row['username']; // or whatever the username field is
}
?>
Posted: Wed Mar 07, 2007 7:12 pm
by ianhull
Thanks everah
one thing though,
Code: Select all
<?php
$checkUser = mysql_query("SELECT * FROM users WHERE username = '$username' and password = '$password'") or die(mysql_error());
while ($row = mysql_fetch_array($checkUser)
{
// Everything you want is in the $row array now
$_SESSION['username'] = $row['username']; // or whatever the username field is
}
?>
//I was thinking of maybe something like this
$_SESSION['userDetails'][] = $row['I DONT KNOW THIS NAME'];
//My idea is to make this so that if new columns are added to the database in the future my php file will hold the data in the session, I will not have to keep going back through the files.
you get what I mean?
Thanks
The example on this page is exactly what I want but I need it storing
in the session
http://www.w3schools.com/php/func_mysql_fetch_array.asp
Would this work?
Code: Select all
while ($_SESSION['userDetails'][] = mysql_fetch_array($checkUser)
[/syntax]
Posted: Wed Mar 07, 2007 7:59 pm
by RobertGonzalez
You could do:
Code: Select all
<?php
$checkUser = mysql_query("SELECT * FROM users WHERE username = '$username' and password = '$password'") or die(mysql_error());
while ($row = mysql_fetch_array($checkUser))
{
// Everything you want is in the $row array now
$_SESSION['userData'][] = $row; // or whatever the username field is
}
?>
Or you could just do:
Code: Select all
<?php
$checkUser = mysql_query("SELECT * FROM users WHERE username = '$username' and password = '$password'") or die(mysql_error());
$_SESSION['userData'][] = mysql_fetch_array($checkUser);
?>
Of course I made the assumption that you tried these and they didn't work. What have you tried and what is or is not working?
Posted: Wed Mar 07, 2007 8:10 pm
by ianhull
Thanks for your help on this one
Here is what I have tried
Code: Select all
$_SESSION['userDetails'][] = mysql_fetch_array($checkUser);
foreach ( $_SESSION['userDetails'] as $key => $val )
echo "<p>{$key}: {$val}</p>";
Code: Select all
$_SESSION['userDetails'][] = mysql_fetch_array($checkUser);
foreach ( $_SESSION['userDetails'][] as $key => $val )
echo "<p>{$key}: {$val}</p>";
This one shows me everything I need perfectly but I do not know how to access it to place it in the session
Code: Select all
print_r(mysql_fetch_array($checkUser));
Code: Select all
print_r(mysql_fetch_array($checkUser));
echo $checkUser[0];
Posted: Wed Mar 07, 2007 9:09 pm
by ianhull
Code: Select all
while ($line = mysql_fetch_array($checkUser, MYSQL_ASSOC)) {
foreach ($line as $_SESSION['userDetails'][]) {
}
}
foreach ( $_SESSION['userDetails'] as $key => $val )
echo "<p>{$key}: {$val}</p>";
I managed to do it with the above code.
Thanks for your input!

Posted: Thu Mar 08, 2007 11:06 am
by RobertGonzalez
You are expecting one row returned from the query, correct? If so, then you don't need to keep looping like that. You can literally read the result into an array and foreach it...
Code: Select all
<?php
// You may want to consider a limit clause on this...
$sql = "SELECT * FROM users WHERE username = '$username' and password = '$password' LIMIT 1";
if (!$result = mysql_query($sql))
{
die('Could not run the query: ' . mysql_error());
}
// Its a good idea to make sure you have a result count
if (mysql_num_rows($result))
{
$row = mysql_fetch_array($result);
foreach ($row as $k => $v)
{
$_SESSION['userData'][$k] = $v;
}
}
?>