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;
  }
}
?>