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

Question

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...

you need to write NAME LIKE "%".$somevariable."%" NOT name = — Sahin Yanlık
– Sahin Yanlık, Commented May 5, 2014 at 13:41

Ian · Accepted Answer · 2014-05-05 13:42:48Z

1

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.

answered May 5, 2014 at 13:42

Ian

3,7064 gold badges32 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tony Peitchev Over a year ago

Originally the code was with LIKE and wasn't returning results, I just tried = and forgot to remove it..

Ian Over a year ago

At the top of the document, can you add error_reporting(E_ALL) .An error might be being thrown

Tony Peitchev Over a year ago

I put it inside the first <?php tag but nothing seemed different upon execution...

Pedro Estrada · Accepted Answer · 2014-05-05 13:54:12Z

0

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
    }
}

edited May 5, 2014 at 13:54

answered May 5, 2014 at 13:49

Pedro Estrada

2,4192 gold badges17 silver badges24 bronze badges

1 Comment

Tony Peitchev Over a year ago

I cannot manage to connect like you :( Maybe because I don't have a password, I don't know...

Shashikumar Misal · Accepted Answer · 2014-05-05 13:55:57Z

0

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...

edited May 5, 2014 at 13:55

answered May 5, 2014 at 13:50

Shashikumar Misal

5092 silver badges16 bronze badges

1 Comment

Tony Peitchev Over a year ago

Thank you, I will check it out, but the "while" does not work like that either - no errors, but again "No Results Found"..I noticed that phpStorm says SQLSRV_FETCH_ASSOC is an undefined constant, though... :/

Tony Peitchev · Accepted Answer · 2014-05-07 13:35:29Z

0

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

answered May 7, 2014 at 13:35

Tony Peitchev

391 silver badge5 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related