I'm trying to write a code where all SQL statements are stored inside a separate PHP file so I can call them later as functions.
I've tried different things but I can't seem to make it work properly.
Here is my code (I'm trying to make a login page here)
login.php
<?php
include('sql.php');
$sql = new sql();
?>
<form class = "form-inline" method = "post" style="color: #FFF; position: absolute; margin-top:100px; margin-left:500px;">
<table width="100%" border="0">
<tr>
<td>Username</td>
<td><input type="text" id = "username" name = "username" class="large" placeholder=""></td>
</tr>
<tr>
<td>Password</td>
<td><input type="password" id = "password" name = "password" class="large" placeholder=""></td>
</tr>
</table>
<br />
<center><button type="submit" id = "submit" name = "submit" class="btn btn-primary">Log in</button></center>
</form>
<?php
//echo $sql->admin();
?>
<?php
if(isset($_POST['submit'])) {
// username and password sent from form
$username=$_POST['username'];
$password=$_POST['password'];
// To protect MySQL injection (more detail about MySQL injection)
$username = stripslashes($username);
$password = stripslashes($password);
$username = mysql_real_escape_string($username);
$password = mysql_real_escape_string($password);
//THIS IS THE PART WHERE I CALL THE SQL STATEMENT FROM A SEPARATE PHP FILE
echo $sql->admin($username,$password);
// Mysql_num_row is counting table row
$count=mysql_num_rows($sql);
// If result matched $username and $password, table row must be 1 row
if($count==1)
{
//unset($_SESSION);
$row = mysql_fetch_assoc($sql);
$_SESSION[logged] = $row[logged];
// Register $username, $password and redirect to file "index.php"
$_SESSION['username'] = $username;
$_SESSION['password'] = $password;
$_SESSION['submitted'] = $row[submitted];
$_SESSION['date_submitted'] = $row[date_submitted];
session_register($_SESSION['username']);
session_register($_SESSION['password']);
?>
<script>window.location="index.php";</script>
<?php
}
else
{ ?>
<center><span style="color:white;">Wrong Username or Password</span></center>
<?php
}
}
?>
sql.php
<?php
include('connect.php');
class sql{
function sql()
{
}
function admin($username,$password)
{
echo $sql=mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' ");
}
}
?>
The error that appears with this code is
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in login.php on line 40
my line 40 is
$count=mysql_num_rows($sql);
I've finally got it. As you said, I've added a line in my sql.php which is
return $result;
another reason why my code didn't work was because I didn't place my
$sql->admin($username,$password);
inside a variable. Now it looks like this
$a = $result->admin($username,$password);
and it's working now.
You should return result set in your function
function admin($username,$password)
{
$result=mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' ");
return $result; // return result set back
}
Your admin function should return a resultset of data:
function admin($username,$password)
{
$result = mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' ");
return $result;
}
By calling it like so:
$resultset = admin($username, $password);
You can then pass this resultset (not the sql) to mysql_num_rows:
$count = mysql_num_rows($resultset);
Like GBD told, you have to return the result set from the mysql query. Furthermore, you have to use the result in your script to handle the submission from the login page.
$result = admin($username,$password)
// Mysql_num_row is counting table row
$count = mysql_num_rows($result)
In login.php you used
//THIS IS THE PART WHERE I CALL THE SQL STATEMENT FROM A SEPARATE PHP FILE
echo $sql->admin($username,$password);
The parameters are send correctly, but the result of SQL should be returned from the function.
You used echo in sql.php as well as in login.php
Add return statement
function admin($username,$password)
{
$sql=mysql_query("SELECT * FROM admin WHERE admin_username= '$username' and admin_password= '$password' ");
return $sql;
}
in sql.php
$result=$sql->admin($username,$password);
and then use the variable $result as $sql is already used for CLASS
This should work.
As my predecessors said, you need to return the value from the admin function.
Besides, why do you call DB table 'admin' ? Do you plan on having multiple admins ? You shouldn't create separate tables for different types of users. You may create one table 'users' with column 'type' where you specify if he is an admin or regular user, and set his privileges based on this value.