Forum Moderators: coopster
Firstly may I say this is one of the most helpful forums I have ever been. I've just joined but I've been an avid fan of this website for ages, but been too scared to post because of its elite status. That's the impression I get, hope im not being rude.
Ok here goes.
As i said i would like to add a search facility to my site where by users can quickly search for a product and after submitting it will return the closest matches to the search term. In the form of a list.
Here is the code of my html form
<form name="form1" id="form1" method="POST" action="searchDataBase.php">
<ul style="list-style-type:none;">
<li><input type="text" name="search" id="search" ></li>
<li><input type="image" name="submit" value="submit" src="images/search-bttn.png" id="submit" title="Click here to search"></li>
</form>
Here is the code inside searchDataBase.php
<?php
require_once("includes/connection.php"); $search = mysql_real_escape_string($_POST['search']);
$query= "SELECT * FROM products WHERE 'name' LIKE '%$search%'";
$result = mysqli_query($db, $query)or die(mysql_error() . "<p>With query:<br>$query");
while ($row = mysql_fetch_assoc($query)){
echo $row['name'].'<br>';
}
?>
When I submit after entering a search term i am presented with
Warning: mysqli_query() expects parameter 1 to be mysqli, null given in E:\wamp\www\drinkpromo\searchDataBase.php on line 7With query:
SELECT * FROM products WHERE 'name' LIKE '%cars%'
If anyone can point me in the right direction i would be very much appreciative.
Kind regards
Dazzclub
Inside of includes/connection.php are you setting $db using either $db = mysqli_connect(...); or $db = new mysqli(...);? Or is that written some other way?
I have just looked at the code in my connection.php
and it looks like i am not using either
here is the code of connection.php
<?php
require("constants.php");
//connection the database
$connection = mysql_connect("localhost", "root", "password");
if (!$connection){
die("Database connection failed; " . mysql_error());
}
//2 select the database
$db_select = mysql_select_db("mywebste", $connection);
if(!$db_select){
die("Database selection failed; ". mysql_error());
}
?>
I have read some where you may need to write a connection such as the one you have mentioned, ah i think its from php.net.
Either your msqli_query call needs to be changed to mysql_query, or your connection and database selection should use
mysqli_connect and
mysqli_select_db
Personally, I would suggest using mysqli as it allows for access to more mysql server functionality( or so I hear).
As far as being hesitant to ask questions on this forum: hesitation is devastation, you won't know until you ask( or read pages and pages of documentation )
Thanks for the replies. I think will have to read upon "mysqli_" as i dont think inserting an i in to my original mysql_connet will sort this out, nothing is ever that simple.
I'll let you guys know how I get on with this asap.
Thanks again.
some feed back regarding mysqli_connect.
As i originally thought, adding an additional i to mysql_connect, failed to work,
i got this response;
-----
Warning: mysqli_select_db() expects parameter 1 to be mysqli, string given in E:\wamp\www\drinkpromo\includes\connection.php on line 9
Database selection failed;
-----
I then dug around and noticed that the mysqli_connect, had selected that dabase along with the connection all in one go. Making any sense?
Let me explain i had always connected and selected a database like this;
----
<?php
require("constants.php");
//connection the database
$connection = mysql_connect("localhost", "root", "password");
if (!$connection){
die("Database connection failed; " . mysql_error());
}
//2 select the database
$db_select = mysql_select_db("database", $connection);
if(!$db_select){
die("Database selection failed; ". mysql_error());
}
?>
----
When i mean all in one go, the connections, aswell as featuring, localhost, root, and password for database. It also featured the name of the database.
So what i did was to edit the connection.php like this;
-----
<?php
require("constants.php");
//connection the database
$connection = mysqli_connect("localhost", "root", "password", "database");
if (!$connection){
die("Database connection failed; " . mysql_error());
}
?>
----
And this displays the site.
Have I gone the right way about this? I also need to edit my queries aswell.
Kind regards
Dazzclub
After thinking everything is ok, I then proceeded to explore my site. The majority of my pages simply pull out the database info and then displays it, so nothing to confusing.
original code;
--------
<?php
$query = "SELECT `name` , `type` , `image` , `category` FROM products ORDER BY product_id DESC" ;
If ($r = mysql_query ($query)) {
//sending the query to the mySQL server
While ($row = mysql_fetch_array($r)) {
//inputs the data into the table
$name = $row['name'];
$type = $row['type'];
$image = $row['image'];
$category = $row['category'];
?>
-----
When I visited a page, just where the original query began, it displayed;
----
Warning: mysql_query() [function.mysql-query]: Access denied for user 'ODBC'@'localhost' (using password: NO) in E:\wamp\www\drinkpromo\game-cards-and-labels.php on line 36
Warning: mysql_query() [function.mysql-query]: A link to the server could not be established in E:\wamp\www\drinkpromo\game-cards-and-labels.php on line 36
----
Arrgh, I thought to myself! My next step was to find out more about
mysqli_query and 'ODBC'@'localhost'.
To sort out the password problem I ran mysql cmd and gave all privileges to ODBC@localhost followed by the password.
code;
----
mysql> GRANT ALL PRIVILEGES ON *.* TO ODBC@localhost
mysql> IDENTIFIED BY 'pppp' WITH GRANT OPTION;
----
After that, I then simply changed mysql_query to mysqli_query, which inturn displayed this;
-----
Warning: mysqli_query() expects at least 2 parameters, 1 given in E:\wamp\www\drinkpromo\game-cards-and-labels.php on line 36
-----
I remembered when i first started to learn php is that you dont need to always inlcude the connection, such as $db because the php assumes it. But because i had changed it, i thought it would be best to insert $connection.
This resulted in displaying an erroring regarding my mysql_fetch_array. I instantly added the i where needed and all is now ok.
Question is have i gone the right way about solving this?
Kind regards
Dazzclub
Here is what the script now looks like;
--------------
<?php
require_once("includes/connection.php");
$search = mysqli_real_escape_string($connection, $_POST['search']);
$query = "SELECT * FROM articles WHERE id LIKE \"%$search%\" OR body LIKE \"%$search%\" OR title LIKE \"%$search%\" ORDER BY id DESC" ;
$result = mysqli_query ($connection, $query)or die(mysqli_connect_errno() . "<p>With query:<br>$query");
while ($row = mysqli_fetch_assoc($query)){
echo $row['name'].'<br>';
}
?>
--------------
When I try it out it displays;
-------
Warning: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, string given in E:\wamp\www\drinkpromo\searchDataBase.php on line 9
-------
If anyone can help it would be great, in the mean time i shall do some research.
Kind regards
Dazzclub
I instantly added the i where needed and all is now ok.
This was definitely the right thing to do. Now, if possible, "forget" the other type of function even exists. :) As you've learned, they don't play well together.
You really should revoke the privileges you granted to ODBC@localhost. What you did there was granted privileges to the default user. The only reason that error message showed up is because the mysql_query couldn't see the connection made by the mysqli_connect. So it attempted to connect using default settings.
In your final code, you just need to swap out what variable you pass to mysqli_fetch_assoc()... Right now you are passing the string $query instead of the return from the mysqli_query, $result.
while ($row = mysqli_fetch_assoc([i]$result[/i])){
I shall evoke the privileges i gave to ODBC@localhost straight away.
I have now swapped the variables, from $query to $result.
I tested it out and the search page showed up blank, just a white page. I looked closer at the code and noticed in the $query it was using id after WHERE, so what i did here was to replace this with the $search variable.
So when i test it, it shows this.
--
With query:
SELECT * FROM products WHERE darren LIKE "%darren%" OR name LIKE "%darren%" OR type LIKE "%darren%" ORDER BY product_id DESC
---
That has to be good right? its not showing an errors now.
Here is what the script inside searchDataBase.php looks like;
----
<?php
require_once("includes/connection.php");
$search = mysqli_real_escape_string($connection, $_POST['search']);
$query = "SELECT * FROM products WHERE $search LIKE \"%$search%\" OR name LIKE \"%$search%\" OR type LIKE \"%$search%\" ORDER BY product_id DESC" ;
$result = mysqli_query ($connection, $query)or die(mysqli_connect_errno() . "<p>With query:<br>$query");
while ($row = mysqli_fetch_assoc($result)){
echo $row['name'].'<br>';
}
?>
Is there anything there you can see looks out of place?
Im trying to sort it out now aswell.
Thanks for your help.
NEWS FLASH, the code now looks like this after some quick modifications.
------
<?php
require_once("includes/connection.php");
$search = mysqli_real_escape_string($connection, $_POST['search']);
$query = "SELECT * FROM products WHERE product_id LIKE \"%$search%\" OR name LIKE \"%$search%\" OR type LIKE \"%$search%\" ORDER BY product_id DESC" ;
$result = mysqli_query ($connection, $query)or die(mysqli_connect_errno() . "<p>With query:<br>$query");
while ($row = mysqli_fetch_assoc($result)){
echo $row['name'].'<br>';
}
?>
--
And instead of displaying the sql query it now displays the matches. Waahooo, I'm getting somewhere.
I`ll have to do more research but its a start eh.
Thanks for your help with this, i can at least decrease the amount of coffee i`ll be having tonight.
Kind regards
Dazzclub
