CODEWITHSUNDEEP
phpsql-server
Tony Peitchev
Tony Peitchev

Reputation: 39

PHP connects to Sql server, but queries don't work

I apologize if my question has a simple answer, but I'm new to this, and am kinda stuck... So I am supposed to make a cooking recipes website for a project and I am using PHP 5.5.9 and SQL server 2012. I have followed a video tutorial to create a search box with is supposed to list results, retrieved from the database. Here is my code:

<?php
include ("dbconnect.php");

if (!isset($_POST['search']))    {
    header("Location:index.php");
}
$search_sql="SELECT * FROM Recipe WHERE name LIKE '%".$_POST['search']."%' OR description LIKE '%".$_POST['search']."%'";
$search_query=sqlsrv_query($conn, $search_sql);
if (sqlsrv_num_rows($search_query)!=0) {
    $search_rs=sqlsrv_fetch_assoc($search_query); }
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>Untitled Document</title>
</head>
<body>
<h3>Search results</h3>
<?php
if (sqlsrv_num_rows($search_query)!=0) {
    do { ?>
        <p><?php echo $search_rs['name']; ?></p>
    <?php }
    while ($search_rs=sqlsrv_fetch_assoc($search_query));
}
else {
    echo "No results found";
}
?>
</body>
</html>

And here is my dbconnect.php:

<?php
$serverName = "USER\SQLEXPRESS"; //serverName\instanceName

// Since UID and PWD are not specified in the $connectionInfo array,
// The connection will be attempted using Windows Authentication.
$connectionInfo = array( "Database"=>"iCook project");
$conn = sqlsrv_connect( $serverName, $connectionInfo);

if( $conn ) {
    echo "Connection established.<br />";
}else{
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
}
?>

After installing drivers for php 5.5 and SQL server, the database did manage to connect..However, the query simply does not work, even if I hard code the values. I don't get errors anymore, but all the time "No results found"...It might be worth mentioning that the tutorial I followed was using MySQL so I changed mysql to sqlsrv in the code. Also, the query DOES execute directly in SQL server...

I would greatly appreciate any help...

EDIT: OK still no luck, but I noticed something - PhpStorm says "Undefined function sqlsrv_fetch_assoc. Declaration of referenced function is not found in built-in library and project files." I tried googling this, but nothing comes up..What do you make of this? Also, the tutorial I've used is this one...

Upvotes: 1

Views: 462

Answers (4)

Tony Peitchev
Tony Peitchev

Reputation: 39

I switched to MySQL and the query works now. Thanks to everybody who tried to help!

Upvotes: 0

Shashikumar Misal
Shashikumar Misal

Reputation: 509

Just change the "while" as below and It will work

while( $search_rs = sqlsrv_fetch_array( $search_query, SQLSRV_FETCH_ASSOC) );

You can also follow the link http://www.php.net/manual/en/function.sqlsrv-fetch-array.php it will help you a lot...

Upvotes: 0

Pedro Estrada
Pedro Estrada

Reputation: 2404

This is how i structure my db_connect.php for SQL Server maybe the problem is in the connection?

<?php 
$server = "localhost"; // aka "localhost" or "192.168.10.110" 
$username = "user"; // aka "ChuckNorris" 
$password = "password"; // aka "cankickyourass" 
$database = "TestDB1"; // database you want to connect to 

$connectionOptions = array("Database" => $database, "UID" => $username, "PWD" => $password);

/* Connect using Windows Authentication. */
$conn = sqlsrv_connect($server, $connectionOptions);

if( $conn ) {
    // Connection Established!
} else {
    echo "Connection could not be established.<br />";
    die( print_r( sqlsrv_errors(), true));
}
?>

and this is how i do a SQL Server query

$sql = "SELECT *
        FROM Users
        WHERE UserName LIKE '%user%'";

// Execute query:
$result = sqlsrv_query($conn, $sql) or die('A error occured: ' . sqlsrv_errors());

if(!$result)
{
    echo 'There are no users. ' . sqlsrv_errors();
}
else
{
    while($row = sqlsrv_fetch_array($result))
    {   
        //use $row['column'] here
    }
}

Upvotes: 0

Ian
Ian

Reputation: 3676

Change

$search_sql="SELECT * FROM Recipe WHERE name ='%".$_POST['search']."%' OR description ='%".$_POST['search']."%'";

To

$search_sql="SELECT * FROM Recipe WHERE name LIKE '%".$_POST['search']."%' OR description LIKE '%".$_POST['search']."%'";

Let me know how it goes.

Upvotes: 1

Related Questions