First. I’m your stereotypical new guy. I have to move a site from using sqlsrv to using mysql. Fortunately, there does not appear to be much sqlsrv code other than the following:
$result = sqlsrv_query($conn,$checkTable);
$data = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC);
The site is being moved from PHP 5.3 to a PHP 7 environment and will be connecting to MySQL instead of SQL server. I have no idea how to recode this small piece.
Converting the adaptor to mysqli or PDO is really only part of the solution. A relatively small part. Arguably the more important piece is determining whether the existing queries are compatible with MySQL. If the application is using SQLServer proprietary features those will need to be refactored to be compatible with MySQL. Also the projects that I have been involved in that use SQLServer favor business logic in the database using stored procedures. If that is the case with your project you will need to decide whether to rewrite those stored procedures in MySQL or migrate that business logic out of the database into the application layer. MySQL applications favor business logic in the application layer rather in stored procedures. This is probably much more involved than you have been made to believe. Is there a separate team working on rebuilding the db/schema and migrating the data to MySQL or will you be doing that to? A SQLServer schema will not be directly compatible with MySQL. Especially if there are proprietary features, views, triggers, and stored procedures being used.
I’m good with MySQL; I use it daily. The queries appear to be quite generic. Here is an example pulled right from the code:
$query = “select * from Logins where Username = '”.$uname.“’ AND Password = '”.$pwd.“'”;
Also, I’ve changed the adapter already and it is working to create the initial connection to the host. In this instance it is actually pointing to the original SQLserver using dblib:
$conn = new PDO( “dblib:host=$server:$port;dbname=$database”, “$username”,“$password”);
I’ve connected successfully to the MySQL server as well but I don’t know the proper syntax to change the code from my original post.
That is one way to go about it. First convert all the procedural sql server function calls to PDO. Once that is done you can easily switch to the new MySQL db given it exists and it is compatible.
Use a combination of searching, manual labor, and testing to go through the entire code base and convert the SQL Server calls to use the new PDO connection. Depending on how the project is built you will need to determine the best method to make the connection instance available to all parts of the application that use it. I’m very much against just changing code without testing. So anywhere where you change code you should test locally to make sure the changes you made didn’t break anything. So depending on how large this code base is this could be a considerable effort.
All of this work is currently being conducted in a test environment. Our live system is intact and in use.
I’m just saying when you change lines of code they should be tested. Even for seemingly trivial things like this. People in the past have recommended automated search techniques without testing every step of the way.
This is reasonably basic PDO stuff - all it does is execute the query, and then retrieve the results, probably within a loop depending on the query. Any basic PDO tutorial will show the correct way to do that.
I replaced the code in question with this code and I’m now able to fetch the array.
$data = $conn->query ($query);
while($result = $data->fetch(PDO::FETCH_ASSOC)):
var_dump($result);
endwhile;
STOP doing that. while() loops have no business any more in fetching database data. The proper way would be:
$stmt = $conn->query($query);
// assuming you have set PDO::FETCH_ASSOC as the default fetch mode
foreach ($stmt as $row) {
// ...
}
and one more, you don’t need to use a loop to get the result as array, that’s what fetchAll() is for.
do you have any further information (e.g. links) about the pro’s and con’s?
I don’t know about pros and cons, but why take an array that already exists and use it to populate another array when you can simply use the existing array?
Probably an insignificant difference for small result sets, but the double memory might be more of a problem the larger it gets.
I updated my code using the example provided by Dormilich. It is producing the result that I’m looking for.
$stmt = $conn->query ($query);
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
foreach ($stmt as $result)
{
print_r($result);
}
Is this more in line with today’s coding?
Hi there @jcorbett
<off-topic>
When you post code in the forum, you need to format it. To do so you can either select all the code and click the </> button, or type 3 backticks ``` on a separate line both before and after the code block.
</off-topic>
I’ve done this for you this time.
Thank you.
tip: setting the default fetch mode globally
$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.