Solved

mysqli query Not returning rows but error of mysql_query() expects parameter 2 to be resource

Posted on 2011-09-06
6
394 Views
Last Modified: 2012-05-12
I worked with this problem for about 3 hours.  So-called soluiions do Not work and some are mixed mysqli  and mysql.  Below is some code that gives the error of "mysql_query() expects parameter 2 to be resource".  I have no idea what resource they are talking about!

$conn1 = new mysqli($hostName1, $userName2, $password2, $databaseName1) ;
/* check connection */
      if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit(); // <==///========
      }
    echo ' database ' . $databaseName1 . ' is now connected to program. ' ;
   
      // Create SQL code to insert a record into the Prospect table.
      $SQL1 = "INSERT INTO Prospect (CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile) ";
      $SQL1 .= " VALUES('N', 'Y', 'John', 'Hammer', 'John@HammondTrees.com', 'Test only in 2011' )";
          
   $conn1->query($SQL1); // run the query
      printf (" |  New Record has id %d.\n", $conn1->insert_id);
      echo ' \  Prospect table ' ;
// this works OK, but not the Select section below
      
      // use a Select to show current records
      $SQL1 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
      $SQL1 = " FROM Prospect " ;
      // $result1 = $conn1->query($SQL1) ;
      // $result1 = mysql_query( $link1, $SQL1) ;
      // associative array
      // $row22 = mysqli_fetch_array($result1, MYSQLI_ASSOC);
      
       echo "CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
       
       // $last1  = mysql_query(   $SQL1 , $link1) ;
      $last1  = mysql_query(   $SQL1  ) ;
       $totalRows1 = mysql_num_rows($last1);
       while ( $addToArray1 = mysql_fetch_array($last1) )
       {
                // This could be placed in a table.
                echo " " . $row['CustomerYN'] . "  " ;
                echo "<br />" ;
       }
0
Comment
Question by:KennethSumerford1
  • 3
  • 2
6 Comments
 
LVL 9

Expert Comment

by:jeff_01
ID: 36493780
I think the issue is here

 $SQL1 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
      $SQL1 = " FROM Prospect " ;


TRY


 $SQL1 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
 $SQL1 .= " FROM Prospect " ;

0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36494004
You can't mix 'mysql' and 'mysqli' functions and expect thing to work right.  This page http://us2.php.net/manual/en/mysqli.query.php shows two different ways to use 'mysqli' and this page http://us2.php.net/manual/en/function.mysql-query.php shows a similar thing for 'mysql' .  If you try to mix the two different methods, your results will be unpredictable.
0
 
LVL 83

Accepted Solution

by:
Dave Baldwin earned 250 total points
ID: 36494079
I think this will work for you.  There are at least two other ways of doing this.
$conn1 = new mysqli($hostName1, $userName2, $password2, $databaseName1) ;
/* check connection */
if (mysqli_connect_errno()) {
	printf("Connect failed: %s\n", mysqli_connect_error());
	exit(); // <==///========
	}
echo ' database ' . $databaseName1 . ' is now connected to program. ' ;
   
// Create SQL code to insert a record into the Prospect table.
$SQL1 = "INSERT INTO Prospect (CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile) ";
$SQL1 .= " VALUES('N', 'Y', 'John', 'Hammer', 'John@HammondTrees.com', 'Test only in 2011' )";
          
$conn1->query($SQL1); // run the query
printf (" |  New Record has id %d.\n", $conn1->insert_id);
echo ' \  Prospect table ' ;
// this works OK, but not the Select section below
      
// use a Select to show current records
$SQL2 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
$SQL2 .= " FROM Prospect " ;
$result2 = $conn1->query($SQL2) ;
while($row22 = $result2->fetch_array(MYSQLI_ASSOC))) {
      
echo "CustomerYN = ".$row22["CustomerYN"].", ActiveYN = ".$row22["ActiveYN"].", ";
echo "FirstName = ".$row22["FirstName"].", LastName = ".$row22["LastName"].", "
echo "EmailMain = ".$row22["EmailMain"].", CoProfile = ".$row22["CoProfile"]."<br>";
}

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:jeff_01
ID: 36494219
Either way the query will not work with the "." missing as effectively the query would be just  

mysql_query("FROM prospect");

instead of

mysql_query("SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile FROM prospect");



0
 

Author Closing Comment

by:KennethSumerford1
ID: 36497701
I had to add two ;'s and then it worked great!  Thanks soooo much!  The code was accurate, complete and easy for a beginner PHP programmer to understand.  I have more than 10 years experience with VB but PHP has some weird coding, at times.  I did take your code, make a few edits and create the function below.

function fnAddRecSeletRecs($hostName1, $userName3, $password3, $databaseName3 ) {
            // Add one record and display all records in the Prospect table.
            
      try {
            $conn1 = new mysqli($hostName1, $userName3, $password3, $databaseName3) ;
      /* check connection */
      if (mysqli_connect_errno()) {
            printf("Connect failed: %s\n", mysqli_connect_error());
            exit(); // <==///========
            }
      echo ' database ' . $databaseName3 . ' is now connected to program. ' ;
        
      // Create SQL code to insert a record into the Prospect table.
      $SQL1 = "INSERT INTO Prospect (CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile) ";
      $SQL1 .= " VALUES('N', 'Y', 'John', 'Hammer', 'John@HammondTrees.com', 'Test only in 2011' )";
                
      $conn1->query($SQL1); // run the query
      printf (" |  New Record has id %d.\n", $conn1->insert_id);
      echo ' \  Prospect table ' ;
      // this works OK, but not the Select section below
            
      // use a Select to show current records
      $SQL2 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
      $SQL2 .= " FROM Prospect " ;
      $result2 = $conn1->query($SQL2) ;
      $K1 = 0 ;

      while($row22 = $result2->fetch_array(MYSQLI_ASSOC)) {
            $K1 = $K1 + 1 ;
      echo $K1 . "  ";      
      echo "CustomerYN = ".$row22["CustomerYN"].", ActiveYN = ".$row22["ActiveYN"].", ";
      echo "FirstName = ".$row22["FirstName"].", LastName = ".$row22["LastName"].", ";
      echo "EmailMain = ".$row22["EmailMain"].", CoProfile = ".$row22["CoProfile"]."<br>";
      } // == end of while
      
      /* close connection */
      $conn1->close();
      return true ; // <===///==========
 } // === end of try

 catch ( Exception $e1) {
      echo "  error in database and table operations-- " . $e1 . " | " ;
                return false ; // <===///==========
  }
 } // === end of function ======================
      
 --- Kenneth
0
 
LVL 83

Expert Comment

by:Dave Baldwin
ID: 36497903
You're welcome.  I didn't have a chance to test it since I didn't have a matching database so I'm not surprised I missed something.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now