KennethSumerford1
asked on
mysqli query Not returning rows but error of mysql_query() expects parameter 2 to be resource
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($result 1, 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 />" ;
}
$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($result
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 />" ;
}
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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");
mysql_query("FROM prospect");
instead of
mysql_query("SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile FROM prospect");
ASKER
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($hostNam e1, $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(MYSQ LI_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
function fnAddRecSeletRecs($hostNam
// 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(MYSQ
$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
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.
$SQL1 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
$SQL1 = " FROM Prospect " ;
TRY
$SQL1 = "SELECT CustomerYN, ActiveYN, FirstName, LastName, EmailMain, CoProfile " ;
$SQL1 .= " FROM Prospect " ;