WampServer
Apache, PHP, MySQL
on Windows
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 02, 2008 06:42PM
1)Just trying to perform a search on a particular actor which will bring up his personal information. I also want it to bring up any awards they have won and any movies they appear in.
Here is a list of all my Tables for this part:
actor
starring_list - link entity between actor and movie
movie
award_list - link entity between actor and award
award
2) I just have the one search box and menu box which a user can select whether they are searching an actor, movie title, director, award, etc
3) I want it to bring up anything related to the name/title entered in the search box.
I know i need to make scripts for every outcome but I dont know how to place my scripts into my html for my webpage so it interacts with it
Edited 1 time(s). Last edit at 04/02/2008 07:00PM by Catling.
Here is a list of all my Tables for this part:
actor
starring_list - link entity between actor and movie
movie
award_list - link entity between actor and award
award
2) I just have the one search box and menu box which a user can select whether they are searching an actor, movie title, director, award, etc
3) I want it to bring up anything related to the name/title entered in the search box.
I know i need to make scripts for every outcome but I dont know how to place my scripts into my html for my webpage so it interacts with it
Edited 1 time(s). Last edit at 04/02/2008 07:00PM by Catling.
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
yfastud
(Moderator)
Date: April 03, 2008 01:26AM
You can check Setup Database guide on my personal websiteQuote
yfastud Wrote:
-------------------------------------------------------
> > $dbhost = 'localhost';
> $dbuser = '';
> $dbpass = '';
>
> You have to provide user and password in order to
> connect :-(
But I don't have a username and password
Where do I create one?
It's a little more complicated than you thought, and I suggest you should get a book to implement what you need. Just google for free php ebookQuote
2) I just have the one search box and menu box which a user can select whether they are searching an actor, movie title, director, award, etc
3) I want it to bring up anything related to the name/title entered in the search box.
From Steven's script, I add a simple search so you can get the idea
<html>
<body>
<h1>Search</h1>
<form action="connectionscript.php" method=post>
<table border=0>
<tr>
<td> Search Actor First Name
</td>
<td align="left">
<input type="text" name="fname" size="10" maxlength="20">
</td>
</tr>
</table>
<input type="submit" value="Submit Order">
</form>
</body>
</html>Save the script above as search.html in same folder w/ connectionscript.php, and in this connectionscript.php, add this line
$fname=$_POST['fname'];
Before this line
$dbhost = 'localhost';
And edit this line
$query = 'SELECT * FROM actor';To become
$query = 'SELECT * FROM actor WHERE Forename=$fname';
Have fun,
FREE One A Day
FREE Photo
FREE Games
FREE Websites
FREE Portable GPS
FREE WAMP Guides
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 04:36PM
Could you take a quick look at my script please as I have been scratching my head for two days now as to why I can not get it to work.
<html>
<body>
<?php
//create short variable names
$searchtype=$_REQUEST['searchtype'];
$searchterm=$_REQUEST['searchterm'];
// cut out the whitespace
$searchterm= trim($searchterm);
// testing to see the user entered a search type and a search term
if (!$searchtype || !$searchterm)
{
echo 'Please enter search details. Please go back and try again';
exit;
}
// slashes out control characters
$searchtype= addslashes($searchtype);
$searchterm= addslashes($searchterm);
// make connection to database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
@ $db = mysql_pconnect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
if (!$db)
{
echo 'Error: Could not connect to Database. Please Try Again';
exit;
}
$db=mysql_select_db('moviedatabase') or die("could not connect ".mysql_error);
$query = ("SELECT * FROM `actor` WHERE $searchtype LIKE '%$searchterm%'"
;
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<p>Number of Results found: '.$num_results.' </p>';
// loop to output number of results to the screen
for ($i=0; $i <$num_results; $i++)
{
// test to see that the search type is Actor/Actress
if ($searchtype == "Actor/Actress"
{
echo "<table border=1 >";
echo "<tr><td> Actor ID</td><td>Forename</td><td>Surname</td>><td>Age</td><td>Date of Birth</td><td>Nationality</td><td>Character Names</td><td>Photo</td></tr>";
while($row = mysql_fetch_array($result))
{ echo "<td>{$row[Actor_ID]}</td>";
echo "<td>{$row[Forename]}</td>";
echo "<td>{$row[Surname]}</td>";
echo "<td>{$row[Age]}</td>";
echo "<td>{$row[Date_of_Birth]}</td>";
echo "<td>{$row[Nationality]}</td>";
echo "<td>{$row[Character_Names]}</td>";
echo "<td>{$row[Photo]}</td></tr>";
}
echo "</table>";
//else ( echo 'No results Found: Please go back and try again')
}
}
?>
</body>
</html>
I keep getting this error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\apps\phpmyadmin2.11.5\search.php on line 44
Number of Results found
I think my query is correct in terms of syntax and thats the only thing I thought could be the problem.
What im trying to do is read in the drop down menu searchtype and read in the search text box search term. E.g click on actor in the drop down menu and enter hugh grant in my text box
PLEASE HELP ME
<html>
<body>
<?php
//create short variable names
$searchtype=$_REQUEST['searchtype'];
$searchterm=$_REQUEST['searchterm'];
// cut out the whitespace
$searchterm= trim($searchterm);
// testing to see the user entered a search type and a search term
if (!$searchtype || !$searchterm)
{
echo 'Please enter search details. Please go back and try again';
exit;
}
// slashes out control characters
$searchtype= addslashes($searchtype);
$searchterm= addslashes($searchterm);
// make connection to database
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = '';
@ $db = mysql_pconnect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');
if (!$db)
{
echo 'Error: Could not connect to Database. Please Try Again';
exit;
}
$db=mysql_select_db('moviedatabase') or die("could not connect ".mysql_error);
$query = ("SELECT * FROM `actor` WHERE $searchtype LIKE '%$searchterm%'"
;$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<p>Number of Results found: '.$num_results.' </p>';
// loop to output number of results to the screen
for ($i=0; $i <$num_results; $i++)
{
// test to see that the search type is Actor/Actress
if ($searchtype == "Actor/Actress"
{
echo "<table border=1 >";
echo "<tr><td> Actor ID</td><td>Forename</td><td>Surname</td>><td>Age</td><td>Date of Birth</td><td>Nationality</td><td>Character Names</td><td>Photo</td></tr>";
while($row = mysql_fetch_array($result))
{ echo "<td>{$row[Actor_ID]}</td>";
echo "<td>{$row[Forename]}</td>";
echo "<td>{$row[Surname]}</td>";
echo "<td>{$row[Age]}</td>";
echo "<td>{$row[Date_of_Birth]}</td>";
echo "<td>{$row[Nationality]}</td>";
echo "<td>{$row[Character_Names]}</td>";
echo "<td>{$row[Photo]}</td></tr>";
}
echo "</table>";
//else ( echo 'No results Found: Please go back and try again')
}
}
?>
</body>
</html>
I keep getting this error:
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\wamp\apps\phpmyadmin2.11.5\search.php on line 44
Number of Results found
I think my query is correct in terms of syntax and thats the only thing I thought could be the problem.
What im trying to do is read in the drop down menu searchtype and read in the search text box search term. E.g click on actor in the drop down menu and enter hugh grant in my text box
PLEASE HELP ME
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 05:27PM
does this even work??
for ($i=0; $i <$num_results; $i++)
anyway i reccommend using Match Against for searches like this instead or where like
for ($i=0; $i <$num_results; $i++)
anyway i reccommend using Match Against for searches like this instead or where like
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 05:38PM
stevenmartin99 Wrote:
-------------------------------------------------------
> does this even work??
>
> for ($i=0; $i <$num_results; $i++)
>
>
> anyway i reccommend using Match Against for
> searches like this instead or where like
I don't know if it works because my script doesnt get down that far.
Would you say this would be correct:
$query = "SELECT * FROM `actor` WHERE MATCH(".$searchtype." AGAINST('%".$searchterm."%')";
?
Edited 1 time(s). Last edit at 04/04/2008 05:38PM by Catling.
-------------------------------------------------------
> does this even work??
>
> for ($i=0; $i <$num_results; $i++)
>
>
> anyway i reccommend using Match Against for
> searches like this instead or where like
I don't know if it works because my script doesnt get down that far.
Would you say this would be correct:
$query = "SELECT * FROM `actor` WHERE MATCH(".$searchtype."
AGAINST('%".$searchterm."%')";?
Edited 1 time(s). Last edit at 04/04/2008 05:38PM by Catling.
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 05:54PM
no its wrong....
$query = "SELECT * from actor where MATCH(the name of the row u wanna serach) AGAINST (+".$searchterm" ' IN BOOLEAN MODE)";
full text support needs to be enabaled on ur table in mysql ans MyIsam storage engine needs to be used...
the + is just that u need a plus in from of the word ur searching for..
so if ur search term can be more then one word u one need to break in up before here and adad a + to each word...
so that the string reads in the format "+word1 +word2 +word3 etc"
like this
$words_in_searchterm = explode(" ","$searchterm";
foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(the name of the row u wanna search) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
$query = "SELECT * from actor where MATCH(the name of the row u wanna serach) AGAINST (+".$searchterm" ' IN BOOLEAN MODE)";
full text support needs to be enabaled on ur table in mysql ans MyIsam storage engine needs to be used...
the + is just that u need a plus in from of the word ur searching for..
so if ur search term can be more then one word u one need to break in up before here and adad a + to each word...
so that the string reads in the format "+word1 +word2 +word3 etc"
like this
$words_in_searchterm = explode(" ","$searchterm"
;foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(the name of the row u wanna search) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 06:11PM
Thanks for your help
"full text support needs to be enabaled on ur table in mysql ans MyIsam storage engine needs to be used..
How do I go about making this settings?
"full text support needs to be enabaled on ur table in mysql ans MyIsam storage engine needs to be used..
How do I go about making this settings?
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 06:13PM
go into phpmyadmin... on ur table structure it will tell u watengine is being used. MyIsam or INNODB
NEEDS TO BE MYISAM
to turn on full text support there in edit structure there is little circles to tick for each column to enable it
NEEDS TO BE MYISAM
to turn on full text support there in edit structure there is little circles to tick for each column to enable it
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 06:57PM
Ive changed the engine to MYISAM and put full text on the varchar rows.
I have placed the code in like so:
$db=mysql_select_db('moviedatabase') or die("could not connect ".mysql_error);
$words_in_searchterm = explode(" ","$searchterm";
foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(the name of the row u wanna search) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<p>Number of Results found: '.$num_results.' </p>';
The question I have is the part with the MATCH(the name of the row u wanna search) is I placed the variable $searchtype here because I wanted it to read the drop down menu with what type of search the user was doing like an Actor or a Movie. Just like they do on play .com where you click if u are searching a DVD or music, then you type in your dvd title or music title
Im guessing that this isnt the way of going about it?
Can you advise?
Edited 2 time(s). Last edit at 04/04/2008 07:00PM by Catling.
I have placed the code in like so:
$db=mysql_select_db('moviedatabase') or die("could not connect ".mysql_error);
$words_in_searchterm = explode(" ","$searchterm"
;foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(the name of the row u wanna search) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
$result = mysql_query($query);
$num_results = mysql_num_rows($result);
echo '<p>Number of Results found: '.$num_results.' </p>';
The question I have is the part with the MATCH(the name of the row u wanna search) is I placed the variable $searchtype here because I wanted it to read the drop down menu with what type of search the user was doing like an Actor or a Movie. Just like they do on play .com where you click if u are searching a DVD or music, then you type in your dvd title or music title
Im guessing that this isnt the way of going about it?
Can you advise?
Edited 2 time(s). Last edit at 04/04/2008 07:00PM by Catling.
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 07:10PM
i presume search term is the words they are looking for ie Jennifer Anistion or ONCE and search term is the type... ie Actress or Movie...
if this is how you want to do it then i would use a double search... like
pull back any rows where search term is Jennifer Aniston and Search Type is actress.
but the way ur thinking of doing it should work too as long as ur VALUE of searchterm is the titles of the columns(case sensitve) in the table u want to search
$query = "SELECT * from actor where MATCH($searchtype) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
should give you
$query = "SELECT * from actor where MATCH(Actor) AGAINST ('jennifer aniston' IN BOOLEAN MODE)";
or
$query = "SELECT * from actor where MATCH(Movie) AGAINST ('once' IN BOOLEAN MODE)";
if this is how you want to do it then i would use a double search... like
pull back any rows where search term is Jennifer Aniston and Search Type is actress.
but the way ur thinking of doing it should work too as long as ur VALUE of searchterm is the titles of the columns(case sensitve) in the table u want to search
$query = "SELECT * from actor where MATCH($searchtype) AGAINST ('$new_searchterm' IN BOOLEAN MODE)";
should give you
$query = "SELECT * from actor where MATCH(Actor) AGAINST ('jennifer aniston' IN BOOLEAN MODE)";
or
$query = "SELECT * from actor where MATCH(Movie) AGAINST ('once' IN BOOLEAN MODE)";
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 07:13PM
sorry just realised... u wont have columns called actor and movie!
u will need to do it the first way i said.. search for actors callled xxxxx in the rows...
or movies called xxxx in the row....
understand ?
ill write code to explain it in a second lol
u will need to do it the first way i said.. search for actors callled xxxxx in the rows...
or movies called xxxx in the row....
understand ?
ill write code to explain it in a second lol
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 07:23PM
like this.... seachtype and searchterm should be the name of the columns..
$searchtype and $searchterm are ur varibles they are searching for,
using the + ie +jennifer +aniston will mean it will only find rows with both of these in the searchterm column. ie jennifer aniston or aniston jennifer
$words_in_searchterm = explode(" ","$searchterm";
foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH(searchterm) AGAINST ('$new_searchterm' IN BOOLEAN MODE) ";
so if they search for a actor - jennfier aniston it will only pull back rows with actor in searchtype and jennifer aniston in searchterm
i think this is wat u want
Edited 1 time(s). Last edit at 04/04/2008 07:25PM by stevenmartin99.
$searchtype and $searchterm are ur varibles they are searching for,
using the + ie +jennifer +aniston will mean it will only find rows with both of these in the searchterm column. ie jennifer aniston or aniston jennifer
$words_in_searchterm = explode(" ","$searchterm"
;foreach ($words_in_searchterm as $single_word)
{
$new_searchterm= $new_searchterm.' +'.$single_word;
}
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH(searchterm) AGAINST ('$new_searchterm' IN BOOLEAN MODE) ";
so if they search for a actor - jennfier aniston it will only pull back rows with actor in searchtype and jennifer aniston in searchterm
i think this is wat u want
Edited 1 time(s). Last edit at 04/04/2008 07:25PM by stevenmartin99.
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 07:41PM
stevenmartin99 Wrote:
-------------------------------------------------------
> like this.... seachtype and searchterm should be
> the name of the columns..
> $searchtype and $searchterm are ur varibles they
> are searching for,
>
Its confusing me a touch lol. I apologise for being such a novice lol
The columns I have in actor table that I need to match the searchterm against are:
Forename
Surname
So i think it should be:
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH('Forename''Surname') AGAINST ('$new_searchterm' IN BOOLEAN MODE) ";
Is this right?
-------------------------------------------------------
> like this.... seachtype and searchterm should be
> the name of the columns..
> $searchtype and $searchterm are ur varibles they
> are searching for,
>
Its confusing me a touch lol. I apologise for being such a novice lol
The columns I have in actor table that I need to match the searchterm against are:
Forename
Surname
So i think it should be:
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH('Forename''Surname') AGAINST ('$new_searchterm' IN BOOLEAN MODE) ";
Is this right?
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 07:46PM
no i dont think u can do this,,, did you have to make there names seperate columns?
else you will have to break up the name into two words... and search seperatly.. but wha about people with three names.... ... is it possible to make another column as full name?
else you will have to break up the name into two words... and search seperatly.. but wha about people with three names.... ... is it possible to make another column as full name?
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 07:49PM
What I have been given is these two seperate clumns im afriad. We ignoring the fact people could have three names lol.
I have submitted the design already with these columns so I cant really change it
god, this is so complicated lol
I think you should start charging me for all the help you're giving me lol
Edited 2 time(s). Last edit at 04/04/2008 07:56PM by Catling.
I have submitted the design already with these columns so I cant really change it
god, this is so complicated lol
I think you should start charging me for all the help you're giving me lol
Edited 2 time(s). Last edit at 04/04/2008 07:56PM by Catling.
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
stevenmartin99
(---.b-ras1.blp.dublin.eircom.net)
Date: April 04, 2008 08:10PM
sorry having dinner
submitted it to who? lol
if u want to hope that people write the names in the right way around and only have two names then i would split the searchterm into two words and use first for forename and second for surname,,, its not a great idea but its all u can really do.
use strtok() to seperate ur $searchterm "jennifer aniston" into $Forename "jennifer" and $Surname "aniston"
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH(Forename) AGAINST ('$Forname' IN BOOLEAN MODE) && MATCH(Surname) AGAINST ('$Surname' IN BOOLEAN MODE) ";
submitted it to who? lol
if u want to hope that people write the names in the right way around and only have two names then i would split the searchterm into two words and use first for forename and second for surname,,, its not a great idea but its all u can really do.
use strtok() to seperate ur $searchterm "jennifer aniston" into $Forename "jennifer" and $Surname "aniston"
$query = "SELECT * from actor where MATCH(searchtype) AGAINST ('$searchtype' IN BOOLEAN MODE) && MATCH(Forename) AGAINST ('$Forname' IN BOOLEAN MODE) && MATCH(Surname) AGAINST ('$Surname' IN BOOLEAN MODE) ";
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
Catling
(---.cable.ubr01.telf.blueyonder.co.uk)
Date: April 04, 2008 08:25PM
Im guessing it would be best that I change my columns Forename and Surname in my actor table to Fullname. It will make things easier wont it?
Im just worried about what I would do if the user just typed in just a surname or a forename
Im just worried about what I would do if the user just typed in just a surname or a forename
Re: PLEASE HELP: Trouble connecting to my database
Posted by:
toivo
(---.belrs4.nsw.optusnet.com.au)
Date: April 05, 2008 12:22AM
Hi,
Just my 2c worth re the first error that happened way back with the statement:
$query = ("SELECT * FROM `actor` WHERE $searchtype LIKE '%$searchterm%'";
$result = mysql_query($query);
You could code more defensively and try to find out as early as possible if an error has occured:
$query = 'SELECT * FROM `actor` WHERE actor LIKE "%' . $searchterm . '%"';
$result = mysql_query($query);
if (!$result) {
echo "<p>Unable to execute query: ".$query. "<br />" . mysql_errno() . ": " . mysql_error() . "</p";
}
At least during testing you would see what exactly the query looks like and what the MySQL server thinks about it. When in production, logging of the error into a text file and graceful termination are the standard options, rather than exposing the structure of your application to the users.
Regards,
toivo
Sydney, Australia
Just my 2c worth re the first error that happened way back with the statement:
$query = ("SELECT * FROM `actor` WHERE $searchtype LIKE '%$searchterm%'"
;$result = mysql_query($query);
You could code more defensively and try to find out as early as possible if an error has occured:
$query = 'SELECT * FROM `actor` WHERE actor LIKE "%' . $searchterm . '%"';
$result = mysql_query($query);
if (!$result) {
echo "<p>Unable to execute query: ".$query. "<br />" . mysql_errno() . ": " . mysql_error() . "</p";
}
At least during testing you would see what exactly the query looks like and what the MySQL server thinks about it. When in production, logging of the error into a text file and graceful termination are the standard options, rather than exposing the structure of your application to the users.
Regards,
toivo
Sydney, Australia
Sorry, only registered users may post in this forum.
