getting mysql warning can someone help?

[Digitry Designs]

I am trying to take data from the db and display it in the member area for the logged in user. Here is my code

 

_____________________________________________________________________

 

<?php

 

//connect to the data base

mysql_connect("localhost", "root", "") or die(mysql_error());

mysql_select_db("login") or die(mysql_error());

 

//check to see if user is logged in

if(isset($_COOKIE['ID_my_site']))

 

$username = $_COOKIE['ID_my_site'];

$pass = $_COOKIE['Key_my_site'];

$check = mysql_query("SELECT * FROM users WHERE username = '$username'")or die(mysql_error());

while($info = mysql_fetch_array( $check ))

 

 

//get users login name

$me="SELECT * FROM login Where username = '$username'";

 

//log into new data base

mysql_select_db("time_sheets") or die(mysql_error());

 

//get info for user and parse that info

$query="SELECT * FROM employees Where lastname = '$me'";

$result=mysql_query($query);

$num=mysql_numrows($result);    <<<----------------line 137 ------------->>>

 

mysql_close();

 

echo "<b><center>Database Output</center></b><br><br>";

 

$i=0;

while ($i < $num) {

 

$date=mysql_result($result,$i,"date");

$first=mysql_result($result,$i,"FirstName");

$last=mysql_result($result,$i,"LastName");

$job=mysql_result($result,$i,"JobName");

$timein=mysql_result($result,$i,"TimeIn");

$timeout=mysql_result($result,$i,"TimeOut");

$lunch=mysql_result($result,$i,"Lunch");

$total=mysql_result($result,$i,"TotalHours");

 

echo "<b>$date $first $last</b><br>$job $timein - $timeout Lunch (-$lunch)<br>Total hours for $date = $total<hr><br>";

 

$i++;

}

 

?>

 

___________________________________________________________________

 

I am getting the error:

 

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\login\members.php on line 137

Database Output

 

Does anyone have any ideas or could help me understand how to do this. I am pretty sure my $me variable is not correct. I have the user log in with there last name, then on the time sheet data base use the last name for the where clause in mysql

 

If that makes sense?

 

I would appreciate any help on this. Thank you in advance!!

[Catfish]

$me="SELECT * FROM login Where username = '$username'";

This makes $me a string.

$query="SELECT * FROM employees Where lastname = '$me'";

This puts the string $me into the string $query. Try print() ing $query and you'll probably find a problem that needs fixing.

 

It looks like you need to validate the result of $me, and then pull the value of $me by using a mysql_fetch_ function, for example: mysql_fetch_assoc - http://au.php.net/manual/en/function.mysql-fetch-assoc.php

 

on top of this, i dont think there is a function called mysql_numrows. It is mysql_num_rows. Your script should be throwing a parse error AFAIK.

[Digitry Designs]

  Quote

It looks like you need to validate the result of $me, and then pull the value of $me by using a mysql_fetch_ function, for example: mysql_fetch_assoc

 

I was looking at my login code and figured I would have to use a fetch function when I saw that you responded. I have know idea how to fetch the person logged in though. would mysql_fetch_array work?

 

maybe something like:

 

$check = mysql_query("SELECT * FROM users WHERE username = '".$_POST['username']."'")or die(mysql_error());

 

$info = mysql_fetch_array( $check )

 

$query="SELECT * FROM employees Where lastname = '$info'";

 

Am I on the right track or am i completely lost???

 

  Quote

on top of this, i dont think there is a function called mysql_numrows. It is mysql_num_rows. Your script should be throwing a parse error AFAIK.

 

I see what you where saying. I fixed it first and got the same error. how ever, Dreamweaver said it was blue after I fixed it though. so that was good.

[Catfish]

you're on the right track with mysql_fetch_array, but note what the function returns:

  Quote

array mysql_fetch_array  ( resource $result  [, int $result_type = MYSQL_BOTH  ] )

mysql_fetch_array — Fetch a result row as an associative array, a numeric array, or both

Returns an array of strings that corresponds to the fetched row, or FALSE  if there are no more rows.

 

$info is an array, so you need to use a key name to access the data in it. I personally prefer to use mysql_fetch_assoc() to retrieve the data in an assocative array. So if you did:

$query = "SELECT * FROM employees Where lastname = 'Stanley'";
if ($dbResult = mysql_query($query))
{
   while($recordData = mysql_fetch_assoc($dbResult))
   {
      print_r($recordData); // output raw contents of $recordData
   }
}
else
   print('Could not SELECT data from database. Query sent: '.$query.'<br/>'."\n".'Error given: '.mysql_error().'<br/>'."\n");

 

Inside the while() { } construct, you can access the data of each record returned from the query made. Since we "SELECT *" (select all fields) from the database, you will have all the data for each record returned. So say the employees table has fields named:

 

firstName, lastName, dateOfBirth, position, hourlyRate

 

inside the while () loop you can access each field value for the current record by using:

$recordData['firstName'] or $recordData['lastName'] ... etc.

 

If you don't want to hard code your function to particular field names, you can use function array_keys() to get the field names:

while($recordData = mysql_fetch_assoc($dbResult))
{
   $fieldNames = array_keys($recordData);
   foreach ($fieldNames as $num => $fieldName)
   {
      print($fieldName.' = '.$recordData[$fieldName].'<br/>'."\n");
   }
}

[Digitry Designs]

I am totally confused now! I dont understand too well or maybe not at all what you mean about hard coding and field arrays. I am trying to get the user name (which is the users last name) from the "login" db and place it in a string. Then use that string with the where clause to parse only that users db entries and not all db entries.

 

$username = "select username from login where (the logged in user)";

 

then calling the data base that the user places data

 

$query = "SELECT * FROM employees where $username";

$result=mysql_query($query);

$num=mysql_num_rows($result);

 

and then parse the data that that user has entered

 

this i believe is place the username into string $username and then since the username is actually the users last name, $query is the string in which all fields from the employee table where the logged in user's last name is. Is that correct? I am a bit lost with the mysql_fetch_assoc function (which is the same as fetch_array, but mysql doesnt have fetch_array so it defaults to fetch_assoc when array is used. I am lost here. I know far too little about both php and mysql to get anything from the reference you linked earlier.

 

With What I want, do I need to use sessions? would sessions make my task easier? I simply want to get the logged in users user name and store it in a string to call when I create the $query string.

 

Does this make sense.

 

[Catfish]

ok, i can see what you're doing. you still need to fetch the record data from the result resource.

 

mysql_query -> returns a "resource"

mysql_fetch_array/assoc etc. -> returns an array containing the data of a single record returned in the resource from mysql_query.

 

This means the value or "variable" you get from a mysql_query call you can't do anything with except use mysql_fetch function on it. I'll show you what your code is doing now, and then what you need it to do:

$username = "select username from login where (the logged in user)";
// above line is making a STRING called $username that looks like: "select username from login where (the logged in user)" <- this is not getting the username from the database
$query = "SELECT * FROM employees where $username";
// above line is making another STRING, which has the STRING $username in it, so the string $query actually looks like: "SELECT * FROM employees where select username from login where (the logged in user)"
$result=mysql_query($query);
// above line is sending the string $query to the database as a query -> this will fail as the query is incorrect
$num=mysql_num_rows($result);
// above line is getting the number of rows returned by the mysql_query - currently this is 0 as the query will be failing

 

If you want to use a user's login name to pull their lat name from the login table, then use that last name as a filtering term in a select query on the employees table, you need to do something like this:

$usernameSQL = "select username from login where (the logged in user)";
if ($lastNameResult = mysql_query($usernameSQL)) // if mysql_query is successful
{
   $recordData = mysql_fetch_assoc($lastNameResult); // fetch record data from result resource as associative array
   $usersLastName = $recordData['username']; // assign data from "username" field of record to variable
}
else
   print('Could not select user\'s last name from database. Error given: '.mysql_error().'<br/>'."\n".'Query sent: '.$usernameSQL.'<br/>'."\n");

$query = "SELECT * FROM employees where lastname = '$usersLastName'";
if ($dbResult = mysql_query($query))
{
   // use mysql_fetch_ functions to work on record returned in $dbResult resource
   $num=mysql_num_rows($dbResult);
}
else
   print('Could not SELECT data from database. Error given: '.mysql_error().'<br/>'."\n".'Query sent: '.$query.'<br/>'."\n");

 

If you want to keep the user's last name for future usage through your software, you can assign it to a session variable to make life easier.

[Digitry Designs]

I understand a bit better now, but, the piece of info I am missing and dont know how to get is here

$usernameSQL = "select username from login where (the logged in user///what goes here? how do i tell mysql i want the user that is logged in? )";

 

does that make sense? I know my code and what not is a bit not optimized, like I said I am a newb, but I do appreciate your help very much.

 

  Quote

 

 

$usernameSQL = "select username from login where (the logged in user)";

if ($lastNameResult = mysql_query($usernameSQL)) // if mysql_query is successful

{

  $recordData = mysql_fetch_assoc($lastNameResult); // fetch record data from result resource as associative array

  $usersLastName = $recordData['username']; // assign data from "username" field of record to variable

}

else

  print('Could not select user\'s last name from database. Error given: '.mysql_error().'<br/>'."\n".'Query sent: '.$usernameSQL.'<br/>'."\n");

 

$query = "SELECT * FROM employees where lastname = '$usersLastName'";

if ($dbResult = mysql_query($query))

{

  // use mysql_fetch_ functions to work on record returned in $dbResult resource

  $num=mysql_num_rows($dbResult);

}

else

  print('Could not SELECT data from database. Error given: '.mysql_error().'<br/>'."\n".'Query sent: '.$query.'<br/>'."\n");

 

[/qote]

[Catfish]

oh ok i thought "(the logged in user)" you had removed from you code pastes for security.

 

if you are coming from a login page you are generally passing a username and password from a form to be authenticated. the best way to hold a logged in user's login name is in a session variable, which can be set like: $_SESSION['userLogin'] = $_POST['userLoginName']; if you had passed the user's name at login from a form using method post and the variable was named 'userLoginName'. Only set the session variable after you authenticate the user's login.

 

Then on additional pages, you can get to session variable values by calling session_start() first, then you can use any values you previously set in $_SESSION array. So if you wanted to use my example names above:

if (session_start())
{
   $usernameSQL = "select username from login where username = '".$_SESSION['userLoginName']."'";
}

 

in such as case, i dont see why you would now want to get the username from a database table, since the username in the SESSION array will only be set if the user's login is authentic and to authenticate the login you would need to check the user's login name is real and the password is correct in the first place.

[Digitry Designs]

  Quote

oh ok i thought "(the logged in user)" you had removed from you code pastes for security.

 

if you are coming from a login page you are generally passing a username and password from a form to be authenticated. the best way to hold a logged in user's login name is in a session variable, which can be set like: $_SESSION['userLogin'] = $_POST['userLoginName']; if you had passed the user's name at login from a form using method post and the variable was named 'userLoginName'. Only set the session variable after you authenticate the user's login.

 

Then on additional pages, you can get to session variable values by calling session_start() first, then you can use any values you previously set in $_SESSION array. So if you wanted to use my example names above:

if (session_start())
{
   $usernameSQL = "select username from login where username = '".$_SESSION['userLoginName']."'";
}

 

in such as case, i dont see why you would now want to get the username from a database table, since the username in the SESSION array will only be set if the user's login is authentic and to authenticate the login you would need to check the user's login name is real and the password is correct in the first place.

 

Ok, I added the code but I am sure it is wrong cause I have errors. I am sorry that I am clueless and miserably frustrated. Here is my code from the members page.

 

[

<?php

$dl = "Download as <a href='xls.php'>.xls </a>file!";

echo $dl;

// Connects to your Database


//connect to the data base
mysql_select_db("time_sheets") or die(mysql_error());

//get info and parse that info
if (session_start())
{
   $usernameSQL = "select username from login where username = '".$_SESSION['username']."'";
}

$query="SELECT * FROM employees WHERE $usernameSQL";
$result=mysql_query($query);
$num=mysql_num_rows($result);

mysql_close();

echo "<b><center>Database Output</center></b><br><br>";

$i=0;
while ($i < $num) {

$date=mysql_result($result,$i,"date");
$first=mysql_result($result,$i,"FirstName");
$last=mysql_result($result,$i,"LastName");
$job=mysql_result($result,$i,"JobName");
$timein=mysql_result($result,$i,"TimeIn");
$timeout=mysql_result($result,$i,"TimeOut");
$lunch=mysql_result($result,$i,"Lunch");
$total=mysql_result($result,$i,"TotalHours");

echo "<b>$date $first $last</b><br>$job $timein - $timeout Lunch (-$lunch)<br>Total hours for $date = $total<hr><br>";

$i++;
}

?>

 

Here is the code from the login page

 

<?php
// Connects to your Database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("login") or die(mysql_error());

//Checks if there is a login cookie
if(isset($_COOKIE['ID_my_site']))

//if there is, it logs you in and directes you to the members page
{
$username = $_COOKIE['ID_my_site'];
$pass = $_COOKIE['Key_my_site'];
$check = mysql_query("SELECT * FROM users WHERE username = '$username'")or die(mysql_error());
while($info = mysql_fetch_array( $check ))
{
if ($pass != $info['password'])
{
}
else
{
header("Location: members.php");

}
}
}

//if the login form is submitted
if (isset($_POST['submit'])) { // if form has been submitted

// makes sure they filled it in
if(!$_POST['username'] | !$_POST['pass']) {
die('You did not fill in a required field.');
}
// checks it against the database

if (!get_magic_quotes_gpc()) {
$_POST['email'] = addslashes($_POST['email']);
}
$check = mysql_query("SELECT * FROM users WHERE username = '".$_POST['username']."'")or die(mysql_error());

//Gives error if user dosen't exist
$check2 = mysql_num_rows($check);
if ($check2 == 0) {
die('That user does not exist in our database. <a href=registration.php>Click Here to Register</a>');
}
while($info = mysql_fetch_array( $check ))
{
$_POST['pass'] = stripslashes($_POST['pass']);
$info['password'] = stripslashes($info['password']);
$_POST['pass'] = md5($_POST['pass']);

//gives error if the password is wrong
if ($_POST['pass'] != $info['password']) {
die('Incorrect password, please try again.');
}

else
{

// if login is ok then we add a cookie
$_SESSION['userLogin'] = $_POST['username'];
$_POST['username'] = stripslashes($_POST['username']);
$hour = time() + 3600;
setcookie(ID_my_site, $_POST['username'], $hour);
setcookie(Key_my_site, $_POST['pass'], $hour);



//then redirect them to the members area
header("Location: members.php");
}
}
}
else
{

// if they are not logged in
?>
<form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
<table border="0">
<tr><td colspan=2><h1>Login</h1></td></tr>
<tr><td colspan="2">
<?php

$reg = "Not signed up? <a href='registration.php'>Register </a>here!";

echo $reg;

?>
</td></tr>
<tr><td>Last Name:</td><td>
<input type="text" name="username" maxlength="40">
</td></tr>
<tr><td>Password:</td><td>
<input type="password" name="pass" maxlength="50">
</td></tr>
<tr><td colspan="2" align="right">
<input type="submit" name="submit" value="Login">
</td></tr>

</table>
</form>
<?php
}

?>

 

here is also a screen shot of what I have when sent to the members page. I am afraid though you will come back with something like, there is no way I can do what I want without learning a great deal more or paying someone to write it for me. lol I hope I am close. I do appreciate all your help even if you can no longer do so. Thank you.

 

[attachment deleted by admin]