Problem converting MySql to MySqli

The following code works:
<?php
$conn = mysql_connect('localhost', 'mrbreeze_breeze', 'xxxx');
mysql_select_db("mrbreeze_lansco",$conn);
$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window


The following does not:
?php
$conn = new mysqli('localhost', 'mrbreeze_breeze', 'xxxx', "mrbreeze_lansco");
$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result =  $conn->query($sql);
while ($row = $result->fetch_array()
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window

breeze351Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
I don't see an obvious problem.  What error message are you getting?
http://php.net/manual/en/mysqli-result.fetch-array.php
0
Dave BaldwinFixer of ProblemsCommented:
You're missing a parens at the end of this line...  Should be:
while ($row = $result->fetch_array())

Open in new window

0
Ray PaseurCommented:
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

If you choose different variable names for $conn, you can make simultaneous connections to the SQL engine.  This means you can convert one query at a time.  That might make it easier to test the scripts.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

breeze351Author Commented:
The question is not abandoned.  I've been in the hospital for 2 weeks.  What I thought was groin pull turned out to be a ruptured intestine.
I'll get back on this in a day
Dave:
I check the code and that was my bad copying the code.  It does have a ) at the end.
There are no error messages.  It just doesn't return the data to the jscript that it's calling with MySqli
0
Dave BaldwinFixer of ProblemsCommented:
Make up a simple form page that posts 'TID' to the PHP page and see what it returns.  Add error reporting to that page also.
0
Ray PaseurCommented:
Glad you're out of the hospital!  Hope things are getting better for you.

Two things come to mind.  I just added the code to the code snippet feature, and noticed that the second code snippet does not have a valid "start-PHP" tag.  Please check that.

And when you tell us that something doesn't work, it's helpful to have the SSCCE information - what does the code do that makes you think it doesn't work?  Where is the URL of the test case, so we can see it in action, etc.  What error messages or error logs do you see?

I get concerned when I see code like this:
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))

Open in new window

...because that assumes that mysql_query() worked correctly, and you cannot make that assumption - MySQL (all extensions) can and will fail for reasons that are outside of your control.  Your scripts must test for success or failure with most of the MySQL-related functions.  This article teaches how to do that.  It also teaches how to escape your external variables so you do not run the risk of using invalid data in a query string.
http://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
0
breeze351Author Commented:
Ok, here is the exact code:
------------------------------------------
Original mysql:
------------------------------------------
<?php
//   Check connection to database
$conn = mysql_connect('localhost', 'langsyst_breeze', 'xxxx');
mysql_select_db("langsyst_retail",$conn);

$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window

------------------------------------------
New MySqli
------------------------------------------
<?php
$conn = new mysqli('localhost', 'langsyst_breeze', 'xxxx', "langsyst_demo");
if ($conn->connect_errno)
{
	$err = "Connection Failure: "
           . $conn->connect_errno
           . ' '
           . $conn->connect_error;

	trigger_error($err, E_USER_ERROR);
}
$company_id = $_POST['TID'];
$SqlString1 = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result = $conn->query($SqlString1);
while ($row = $result->fetch_array())
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window

0
Ray PaseurCommented:
Please refer back to this article for examples about how to do these things correctly.  It's just too much to teach in an online forum like E-E because there are so many moving parts that must be discussed and understood.
http://www.experts-exchange.com/articles/11177/PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

Here is the old code snippet:
<?php
$conn = new mysqli('localhost', 'langsyst_breeze', 'xxxx', "langsyst_demo");
if ($conn->connect_errno)
{
	$err = "Connection Failure: "
           . $conn->connect_errno
           . ' '
           . $conn->connect_error;

	trigger_error($err, E_USER_ERROR);
}
$company_id = $_POST['TID'];
$SqlString1 = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result = $conn->query($SqlString1);
while ($row = $result->fetch_array())
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window

And here is how I might write it (untested but probably correct in principle)
<?php
/**
 * http://www.experts-exchange.com/questions/28714791/Problem-converting-MySql-to-MySqli.html#a41090309
 *
 * Connect to a DB server, run a query using a POST request and return the result set in a JSON string
 */
// ENSURE THAT ERRORS, WARNINGS, AND NOTICES ARE ALL RAISED
error_reporting(E_ALL);
ini_set('display_errors', TRUE);

// SET UP THE DEFAULT VALUE FOR THE EXPECTED RETURN VARIABLE
$resultArray = [];

// TRY TO CONNECT OR FAIL ON ERROR
$conn = new mysqli('localhost', 'langsyst_breeze', 'xxxx', "langsyst_demo");
if ($conn->connect_errno)
{
    $err
    = $conn->connect_errno
    . ' '
    . $conn->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}

// ESCAPE THE REQUEST VARIABLE
$company_id = $conn->real_escape_string($_POST['TID']);

// CREATE THE SQL QUERY STRING
$SqlString1 = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";

// RUN THE QUERY AND TEST FOR SUCCESS OR FAILURE
$result = $conn->query($SqlString1);
if (!$result)
{
    $err = 'FAIL: ' . $SqlString1 . ' ' . $conn->error;
    trigger_error($err, E_USER_ERROR);
}

// IF WE GET HERE, THE QUERY SUCCEEDED - COLLECT ALL OF THE RESULTS OBJECT SETS
while ($row = $result->fetch_object())
{
    $resultArray[] = $row;
}

// RETURN THE RESULTS IN THE FORM OF A JSON STRING
echo json_encode($resultArray);

Open in new window

0
breeze351Author Commented:
Ray:
It does not make sense to me.  Your solution only has error checking.  The original code worked.  What am I missing changing this to Mysqli?  I was always told to keep the code as short as possible for best times.
0
Ray PaseurCommented:
The point of adding error checking is to see if there are any errors.  But you still haven't told us what the original problem was!
0
breeze351Author Commented:
Ray:
The problem is that when I switched the original code to MySqli it does not work.

Original Code:
?php
//   Check connection to database
$conn = mysql_connect('localhost', 'langsyst_breeze', 'xxxx');
mysql_select_db("langsyst_retail",$conn);

$company_id = $_POST['TID'];
$sql = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result =  mysql_query($sql);
while ($row = mysql_fetch_array($result))
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window


MySqlI Code:
<?php
$conn = new mysqli('localhost', 'langsyst_breeze', 'xxxx', "langsyst_demo");
if ($conn->connect_errno)
{
	$err = "Connection Failure: "
           . $conn->connect_errno
           . ' '
           . $conn->connect_error;

	trigger_error($err, E_USER_ERROR);
}
$company_id = $_POST['TID'];
$SqlString1 = "SELECT * FROM pers WHERE TID ='$company_id' order by LNAME";
$result = $conn->query($SqlString1);
while ($row = $result->fetch_array())
{
	$resultArray[] = $row;
}
echo json_encode($resultArray);
?>

Open in new window


The difference between the database names from the original code (langsyst_retail) and the MySqli code (langsyst_demo) does not matter.  The demo database is a copy of the retail database.  

The only thing that sticks out to me is the error checking on the MySqli code.

Thanks
Glenn
0
Ray PaseurCommented:
...  it does not work.
To the best of my knowledge, "does not work" is not an error message.  This is like saying "My car won't carry me to the store."  We don't know if your car has any gas, or has been stolen, or has a flat tire, etc.  It's impossible to help you unless we can get some explanation of the error condition.  PHP error messages have line numbers and explanatory information that can help us identify the cause of the problem.

Please, please, please tell us... WHAT does not work?  What is the symptom of the error?  Why are there no checks for the value of $result?  What does the script produce?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
breeze351Author Commented:
Ok.
The login and password are the same for both sites:

Login; My_Login_ID
password: abcxyz

The site that works is: lansco.langsystems.net.  This is the original code running mysql.

The site that does not work is demo.langsystems.net.  This is the code that is using mysqli.

After you have logged in.  Click on exclusives.  Select any listing.  Click on "Edit".  
This will bring up the detail screen.  Look at the drop downs for the personel under "Contactt".  You will see a list of people at that company.  Now change the "Contact" company.  The original code (lansco.langsystems.net - mysql) will repopulate the personel drop down.  If you do the same thing on the other site (demo.langsystems.net - mysqli)
keeps the same personel in the drop down.

I'm not worried about you logging in.  This is test data.

Glenn
0
breeze351Author Commented:
Any answers?
0
breeze351Author Commented:
I still haven't heard anything on this!
0
breeze351Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for breeze351's comment #a41380085

for the following reason:

No answers from my last post
0
breeze351Author Commented:
Leave it open. Maybe someone will answer.
0
breeze351Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for breeze351's comment #a41456887
Assisted answer: 500 points for Ray_Paseur's comment #a41291365

for the following reason:

close
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.