...

View Full Version : Need Fresh Eyes to Find Syntax Error



jchrisphonte
01-26-2012, 12:00 AM
Think I must be missing something....


Here is my error: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/hedgefu1/public_html/hedgevent/search.php on line 86
Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIKE '%hedge%') OR ( LIKE '%hedge%')' at line 1

**** FYI I put stars around line 86******



<?php

// file for database connection
include('inc/db.inc.php');

// configuration file
include('inc/config.inc.php');

if(isset($_GET['p'])) {
$page_number = $_GET['p'];
$arraySearch = $_GET['terms'];
$show_count = $_GET['count'];
settype($page_number, 'integer');
}
$nospaces = substr($_GET['terms'],0,3);
$offset = ($page_number - 1) * $records_number;
// check for an empty string and display a message.
if ($_GET['terms'] == "") {
echo '<div id="counter"></div>';
// minim 3 characters condition
} else if(strlen($_GET['terms']) < $limitchar) {
echo '<div id="counter">'. $limitchar .' characters minimum</div>';
// no spaces in first 3 letters
} else if(preg_replace('/[a-zA-Z0-9]/', '', $nospaces)) {
echo '<div id="counter">Please use letters or numbers in first 3 characters</div>';
} else {

// explode search words into an array
$arraySearch = explode(" ", $_GET['terms']);
// table fields to search
$arrayFields = array(0 => $first_field, 1 => $second_field, 2 => $third_field, 3 => $fourth_field);
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM $table_name WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.") LIMIT $offset, $records_number;";
$search = mysql_query($query);


// get number of search results
$arrayFields = array(0 => $first_field, 1 => $second_field, 2 => $third_field, 3 => $fourth_field);
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM $table_name WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.")";
$count_results = mysql_query($query);
**** $numrows = mysql_num_rows($count_results) or die('Query failed: ' . mysql_error() . "<br />\n$sql");******

// no results
if($numrows == 0) {
echo '<div id="counter">No results found</div>';

// show results
} else {

echo '<div id="results">
<div id="results_top"><p><b>'. $_GET['terms'] .'</b> - '. $numrows .' results found</p></div>
';

while($row = mysql_fetch_assoc($search)) {

$urltitle = str_replace(" ","_", $row['event']);

echo '<div class="item">
<div class="details"><a href="http://www.hedgevent.com/'.$urltitle.'-'.$row['id'].'.html" style="float:left;"></a><a href="http://www.hedgevent.com/'.$urltitle.'-'.$row['id'].'.html" class="title">'.$row['event'].'</a><br />
'.$row['location'].'</div>
<div class="played"><span>'.$row['city'].'</span>
</div>
<div style="clear:both;"></div></div>';
}
// pagination
$maxPage = ceil($numrows/$records_number);

$nav = '';
for($page = 1; $page <= $maxPage; $page++) {
if ($page == $page_number) {
$nav .= "$page";
}
else
{
$nav .= "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">$page</a>";
}
}

if ($page_number > 1) {

$page = $page_number - 1;
$prev = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&laquo;</a>";

$first = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=1')\">First</a>";
}
else {
$prev = '';
$first = '';
}

if ($page_number < $maxPage) {
$page = $page_number + 1;
$next = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&raquo;</a>";

$last = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$maxPage')\">Last</a>";
}
else {
$next = '';
$last = '';
}
echo $data;
echo "<div id=\"results_bottom\"><p>$first $prev $nav $next $last</p></div>
</div>";
}
}
?>

tangoforce
01-26-2012, 12:22 AM
Start by printing the whole query to the browser. Post that here.

jchrisphonte
01-26-2012, 12:31 AM
Not sure what you mean by printing query to my browser... i would be glad to post this if you can help me get there...

appreciate your help Tango.

tangoforce
01-26-2012, 02:18 AM
Not sure what you mean by printing query to my browser... i would be glad to post this if you can help me get there...

appreciate your help Tango.

BEFORE this line:
$count_results = mysql_query($query);

Do this:
print $query;

jchrisphonte
01-26-2012, 02:23 AM
got this back:

SELECT * FROM calendar_event WHERE (event LIKE '%inv%') OR (company LIKE '%inv%') OR ( LIKE '%inv%') OR ( LIKE '%inv%')
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/hedgefu1/public_html/hedgevent/search.php on line 88
No results found

tangoforce
01-26-2012, 02:27 AM
(company LIKE '%inv%') OR ( LIKE '%inv%')


See the difference?

jchrisphonte
01-26-2012, 02:28 AM
not sure if this helps yet this query was designed to search 2 columns.

I added 2 more columns by adding:

2 => $third_field, 3 => $fourth_field (added these 2 extra bits on line 31 and 60 -- ill outline them below with****)



<?php

// file for database connection
include('inc/db.inc.php');

// configuration file
include('inc/config.inc.php');

if(isset($_GET['p'])) {
$page_number = $_GET['p'];
$arraySearch = $_GET['terms'];
$show_count = $_GET['count'];
settype($page_number, 'integer');
}
$nospaces = substr($_GET['terms'],0,3);
$offset = ($page_number - 1) * $records_number;
// check for an empty string and display a message.
if ($_GET['terms'] == "") {
echo '<div id="counter"></div>';
// minim 3 characters condition
} else if(strlen($_GET['terms']) < $limitchar) {
echo '<div id="counter">'. $limitchar .' characters minimum</div>';
// no spaces in first 3 letters
} else if(preg_replace('/[a-zA-Z0-9]/', '', $nospaces)) {
echo '<div id="counter">Please use letters or numbers in first 3 characters</div>';
} else {

// explode search words into an array
$arraySearch = explode(" ", $_GET['terms']);
// table fields to search
*******31******* $arrayFields = array(0 => $first_field, 1 => $second_field, 2 => $third_field, 3 => $fourth_field);
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM $table_name WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.") LIMIT $offset, $records_number;";
$search = mysql_query($query);


// get number of search results
*******60******* $arrayFields = array(0 => $first_field, 1 => $second_field, 2 => $third_field, 3 => $fourth_field);
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM $table_name WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.")"or die('Query failed: ' . mysql_error() . "<br />\n$sql");
print $query;

$count_results = mysql_query($query);
$numrows = mysql_num_rows($count_results);

// no results
if($numrows == 0) {
echo '<div id="counter">No results found</div>';

// show results
} else {

echo '<div id="results">
<div id="results_top"><p><b>'. $_GET['terms'] .'</b> - '. $numrows .' results found</p></div>
';

while($row = mysql_fetch_assoc($search)) {

$urltitle = str_replace(" ","_", $row['event']);

echo '<div class="item">
<div class="details"><a href="http://www.hedgevent.com/'.$urltitle.'-'.$row['id'].'.html" style="float:left;"></a><a href="http://www.hedgevent.com/'.$urltitle.'-'.$row['id'].'.html" class="title">'.$row['event'].'</a><br />
'.$row['location'].'</div>
<div class="played"><span>'.$row['city'].'</span>
</div>
<div style="clear:both;"></div></div>';
}
// pagination
$maxPage = ceil($numrows/$records_number);

$nav = '';
for($page = 1; $page <= $maxPage; $page++) {
if ($page == $page_number) {
$nav .= "$page";
}
else
{
$nav .= "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">$page</a>";
}
}

if ($page_number > 1) {

$page = $page_number - 1;
$prev = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&laquo;</a>";

$first = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=1')\">First</a>";
}
else {
$prev = '';
$first = '';
}

if ($page_number < $maxPage) {
$page = $page_number + 1;
$next = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$page')\">&raquo;</a>";

$last = "<a href=\"javascript:htmlData('search.php','terms=".$_GET['terms']."&amp;p=$maxPage')\">Last</a>";
}
else {
$next = '';
$last = '';
}
echo $data;
echo "<div id=\"results_bottom\"><p>$first $prev $nav $next $last</p></div>
</div>";
}
}
?>

jchrisphonte
01-26-2012, 02:36 AM
here is the related config file if it helps...



<?php

$table_name = "calendar_event"; //which table from database
$first_field = "event"; //which field from table
$second_field = "company"; //which field from table
$third_field = "description";
$fourth_field = "city";
$limitchar = 3; //minimum of characters
$records_number = 100; // Number of records to show per page (different from 0)
$page_number = 1;// default start page
?>

tangoforce
01-26-2012, 03:02 AM
Thats definitely got something to do with it but what I've no idea. It's 2am here now so I'm getting useless for reading code.

I'll try to assist again tomorrow with fresh eyes however I suspect Fou-Lou, myfayt, BluePanther or msleim will probably step in to assist shortly.

jchrisphonte
01-26-2012, 03:08 AM
thank you for all you help -- I appreciate your time very much.

Have a good evening.

Inigoesdr
01-26-2012, 06:31 PM
So did you resolve this? It looks like (http://codepad.org/oVSmZUds) it should work.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum