Jump to content

PHP MySQL help

kanala

By kanala
in PHP

Recommended Posts

kanala Member

kanala

Posted
Posted

my script below doesn't work, it always says "Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/www/takeawaydvds.com/test php.php on line 48"Anyone know what's wrong?

<?php$con = mysql_connect("pdb1.awardspace.com:3306","tky_products","dragoninn");if (!$con)  {  die('Could not connect: ' . mysql_error());  }mysql_select_db("tky_products", $con);$result = mysql_query("SELECT * FROM ProductsWHERE Item No.='1'");while($row = mysql_fetch_array($result))  {  echo $row['Item No.'] . " " . $row['Genre'];  echo "<br />";  }?>

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

The error message you got means that the query failed, it means there is a syntax error. You should check for errors when you do the query.

$result = mysql_query("SELECT * FROM Products WHERE Item No.='1'");if (!$result){  echo "Database error: " . mysql_error();}

The problem in this case is the field name like shlomi guessed.

Link to comment
Share on other sites
kanala Member

kanala

Posted
  • Author
Posted

OK I got a problem with my search box now. It always comes up with a blank page with no results.Here is the search box form:

<form action="search.php" method="post"><input name="search" type="text" /><input type="submit" value="Submit" /></form>

And here is my PHP query:

<?php$dbhost = "pdb1.awardspace.com:3306";$dbname = "tky_products";$dbuser = "tky_products";$dbpass = "dragoninn";$ItemName = $_POST['search'];$query = "SELECT * FROM Products WHERE ItemName LIKE '$ItemName'";$dblink = mysql_connect($dbhost, $dbuser, $dbpass);mysql_select_db($dbname, $dblink);$result = mysql_query($query) or die(mysql_error());$row = mysql_fetch_array($result) or die(mysql_error());while($row = mysql_fetch_array($result)){echo $row['ItemName']. " ", $row['id'];echo "<br />";}?>

Link to comment
Share on other sites
kanala Member

kanala

Posted
  • Author
Posted 
if (mysql_num_rows($result)==0){	header("location:anotherpage.php");}

Where does that go in this?
<?php$dbhost = "pdb1.awardspace.com:3306";$dbname = "tky_products";$dbuser = "tky_products";$dbpass = "dragoninn";$dblink = mysql_connect($dbhost, $dbuser, $dbpass);mysql_select_db($dbname, $dblink);$ItemName = mysql_real_escape_string($_POST['search']);$query = "SELECT * FROM Products WHERE lower(Keywords) LIKE lower('%$ItemName%')";$result = mysql_query($query) or die(mysql_error());$row = mysql_fetch_array($result) or die(mysql_error());while($row = mysql_fetch_array($result)){echo" <table width=\"90%\" background=\"images/bg4.gif\" cellspacing=\"0\" \n";echo" align=\"center\">";echo" <tr>\n";echo" <td width=\"20%\" rowspan=\"2\">\n";echo" <div class=\"product\">\n";echo" <a href=\"http://www.takeawaydvds.com/product.php?id=".$row['id']."\"><img src=\"http://www.takeawaydvds.com/".$row['Image']."\"\n"; echo" width=\"80px\" height=\"80px\"></a></div>\n";echo" </td>\n";echo" <td width=\"80%\" colspan=\"2\">\n";echo" <h1><font size=\"2\"><a href=\"http://www.takeawaydvds.com/product.php?id=".$row['id']."\">".$row['ItemName']."\n";echo" </a></font></h1>";echo" </td>\n";echo" </tr>\n";echo" <tr>\n";echo" <td width=\"40%\" valign=\"top\"><span class=\"dvdp\">Item number: ".$row['id']."</span><br>\n";echo" <span class=\"dvdp\">RRP: ".$row['RRP']."</span><br>\n";echo" <span class=\"dvdp\"><b>Our price: ".$row['Price']."</b></span></td>\n";echo" <td width=\"40%\" align=\"right\">".$row['BuyItNow']."\n";echo" </td>\n";echo" </tr>\n";echo" </table>\n";echo "<br />";}?>

Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

It goes after you create $result from mysql_query, but before you use it with mysql_fetch_array. Also, when you're redirecting you should stop the script, there's no reason for it to keep running.

if (mysql_num_rows($result)==0){	header("location:anotherpage.php");	exit();}

Link to comment
Share on other sites
kanala Member

kanala

Posted
  • Author
Posted
It goes after you create $result from mysql_query, but before you use it with mysql_fetch_array. Also, when you're redirecting you should stop the script, there's no reason for it to keep running.
if (mysql_num_rows($result)==0){	header("location:anotherpage.php");	exit();}

How would I cut off results if there are too many, then place them on the next page?
Link to comment
Share on other sites
justsomeguy w3schools Guru

justsomeguy

Posted
Posted

That's called pagination, you should do a search to find some tutorials on it. Essentially you need a variable that keeps track of which page you're on and how many to show per page, and the query would need to be limited to only return the correct rows for that page. The next and back links would need to have the page variable in them to tell it which page to show.

Link to comment
Share on other sites
Synook w3schools Guru

Synook

Posted
Posted

Also, with pagination you could just have a LIMIT statement that limits the number of results, and then have a GET variable that is passed when you want to go to the next page. So

$resultsPerPage = 20; //The number of results per pageif (isset($_GET['page'])) $start = $resultsPerPage * ($_GET['page'] - 1); //So if the page is 4 then 20 * (4 - 1) = 60else $start = 0; //The starting record}$query = "SELECT * FROM Products WHERE lower(Keywords) LIKE lower('%$ItemName%') LIMIT $start, $resultsPerPage";

Then, later on in your page, to display the page selector, have a code like

$result = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(Keywords) FROM Products WHERE lower(Keywords) LIKE lower('%$ItemName%')"))); //Get the number of matching results, you could also use this query to display the total results$pages = ceiling($result / 20); //Calc the number of pagesecho "Pages: ";for ($i = 1; $i != $pages; $i++) {echo "<a href=\"?page=$i&ItemName=$ItemName\">$i</a>";if ($i < $pages) echo ", ";}

This code would mean that your search string is passed in the URL, as in thepage.php?page=2&ItemName=watchThis code should work, though I haven't tested it. Hope it helps :)

Link to comment
Share on other sites
kanala Member

kanala

Posted
  • Author
Posted
Also, with pagination you could just have a LIMIT statement that limits the number of results, and then have a GET variable that is passed when you want to go to the next page. So
$resultsPerPage = 20; //The number of results per pageif (isset($_GET['page'])) $start = $resultsPerPage * ($_GET['page'] - 1); //So if the page is 4 then 20 * (4 - 1) = 60else $start = 0; //The starting record}$query = "SELECT * FROM Products WHERE lower(Keywords) LIKE lower('%$ItemName%') LIMIT $start, $resultsPerPage";

Then, later on in your page, to display the page selector, have a code like

$result = implode(mysql_fetch_assoc(mysql_query("SELECT COUNT(Keywords) FROM Products WHERE lower(Keywords) LIKE lower('%$ItemName%')"))); //Get the number of matching results, you could also use this query to display the total results$pages = ceiling($result / 20); //Calc the number of pagesecho "Pages: ";for ($i = 1; $i != $pages; $i++) {echo "<a href=\"?page=$i&ItemName=$ItemName\">$i</a>";if ($i < $pages) echo ", ";}

This code would mean that your search string is passed in the URL, as in thepage.php?page=2&ItemName=watchThis code should work, though I haven't tested it. Hope it helps :)

Sorry for making you spend all that time you took writing that code. Unfortunately I found one on Google and it works, but thanks anyway but that bottom line did help. Thanks (thepage.php?page=2&ItemName=watch)I have another question, how do you sort out the results in a specific order e.g. alphabetical or by a date?AND ALSO how do I search the database WHERE field1 OR field2...
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

Go to topic listing
×
×
  • Create New...