Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MSSQL query changed to MySQL shows not valid mysql resource

Posted on 2008-06-12
6
Medium Priority
?
560 Views
Last Modified: 2008-08-05
Alright

I worked on this for hours and I'm stuck. Below I pasted the script from a working MSSQL login which I changed to use with MySQL. I know that there are syntax differences and the hours I spent was replacing some of them in this script but I've got no where.

I keep getting invalid mysql result resource. Usually on ($result=mysql_query($sql);) however it changes to other lines depending the script.

A fix would be great I'm assuming its a quick thing I cant believe I cant get it working from my MySql book and online!
session_start();
  $user="##";
  $host="##";
  $password="##";
  $database="##";
 
mysql_connect("$host", "$user", "$password")or die("cannot connect");
mysql_select_db("$database")or die("cannot select DB");
 
$sql="SELECT * FROM users WHERE username='$_POST[myusername]' AND pass='$_POST[mypassword]'";
$result=mysql_query($sql);
 
// mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
 
if($count==1) //Login details were found so auth
{

Open in new window

0
Comment
Question by:Ryan Bayne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 17

Accepted Solution

by:
nanharbison earned 1200 total points
ID: 21774686
If you are using variables for MySQL functions, don't use quotes. So change
mysql_connect("$host", "$user", "$password")
to
mysql_connect($host, $user, $password)
and change this:
$sql="SELECT * FROM users WHERE username='$_POST[myusername]' AND pass='$_POST[mypassword]'";

to this:
$sql="SELECT * FROM users WHERE username='".$_POST['myusername']."' AND pass='".$_POST['mypassword']."'";

because if you are using post and get variables you have to separate them from the code with a double quote and a dot. I also think you need to add single quotes to $_POST[myusername] inside the brackets, the way I did it in the changed version. I always forget if you HAVE to do it.
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 21774689
the use of double and single quotes, and having to set off variables that are in the form $_POST[] and $_SESSION[], etc always slays me!
0
 
LVL 2

Author Comment

by:Ryan Bayne
ID: 21774799
Well your changes are correct and as you will see in my code below I needed to change the quotes.

I've arrived at a point I did reach myself prior to this question but ended up starting again maybe this time you can help me.

// mssql_num_row is counting table row
$count=mysql_num_rows($result);

That line results in... mysql_num_rows(): supplied argument is not a valid MySQL result resource in
I did think there was a different way to do this with mysql but I'm thinking I dreamed that up!

ideas? thanks so far
<?php
//Processes attempted login
//Retrieves users access permission and fowards to required page depending on that users authorisation
session_start();
 
  $user="##";
  $host="##";
  $password="##";
  $database="##";
 
mysql_connect($host, $user, $password)or die("cannot connect");
mysql_select_db("$database")or die("cannot select DB");
 
$sql='SELECT * FROM users WHERE username="$_POST[myusername]" AND pass="$_POST[mypassword]"';
$result=mysql_query($sql);
 
// mssql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
 
		
if($count==1 && $level=='closed')//Send to account closed page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else if($count==1 && $level=='master')//Send to web master page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=admin/webmaster.php");
}
else if($count==1 && $level=='admin')//Send to basic admin area
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else if($count==1 && $level=='member')//Send to member page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else
{
	header("location: index.php?cell=pagecells/loginfail.php");
	exit();
}
 
?> 

Open in new window

0
Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

 
LVL 2

Author Comment

by:Ryan Bayne
ID: 21774882
I think thats it done. Had to mess around with the quotes and stuff a bit more. Actually I better review what I done I cant remember lol

THANKS
<?php
//Processes attempted login
//Retrieves users access permission and fowards to required page depending on that users authorisation
session_start();
 
  $user="root";
  $host="localhost";
  $password="25130217cmt";
  $database="WebTG2008";
 
mysql_connect($host, $user, $password)or die("cannot connect");
mysql_select_db("$database")or die("cannot select DB");
 
$sql='SELECT * FROM users WHERE username="$_POST[myusername]" AND password="$_POST[mypassword]"';
 
$result=mysql_query($sql);
 
// mssql_num_row is counting table row
$count = mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
 
		
if($count==1 && $level=='closed')//Send to account closed page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else if($count==1 && $level=='master')//Send to web master page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=admin/webmaster.php");
}
else if($count==1 && $level=='admin')//Send to basic admin area
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else if($count==1 && $level=='member')//Send to member page
{
	$_SESSION['auth']="yes";
	header("location: index.php?pagecell=closed.php");
}
else
{
	header("location: index.php?cell=pagecells/loginfail.php");
	exit();
}
 
?> 

Open in new window

0
 
LVL 2

Author Comment

by:Ryan Bayne
ID: 21774888
oops put database details. dont worry its a test db only on testing environment with no data
0
 
LVL 17

Expert Comment

by:nanharbison
ID: 21777404
So you got the mysql_num_rows to work? You get that error when there is something wrong with the query or there is no data returned. You might be better off saying:
if ($count = mysql_num_rows($result)) {

      do something

}


0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question