Welcome to WebmasterWorld Guest from 54.162.163.181

Forum Moderators: open

Message Too Old, No Replies

DB Errors

     
1:26 pm on Dec 8, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


I have a database on my website that a friend built for me years ago (I am DB Illiterate completely)

Anyway the database is for Trivia to be on our web page that i change every day with a new question. Today I noticed an error log file generated and have no idea where to begin with troubleshooting this. As a note, the database currently works perfectly fine both with entering data, searching and displaying current question on my main page.

Here are the details on the errors (there seem to be 2 of them)

[08-Dec-2016 11:36:52 UTC] PHP Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in /home/disneytr/public_html/triviaadmin/searchFunction.php on line 54

Line 52-55 of that php file reads...
//$query .= " AND displayable ='1'";
$searchResult = mysql_query($query);
$num_rows_searchResult = mysql_numrows($searchResult);
// END SEARCH FUNCTION //


The other error reads..
[08-Dec-2016 12:16:42 UTC] PHP Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in /home/disneytr/public_html/search.php on line 111
Line 111 Reads...
//echo "<br>num_rows_searchResult = $num_rows_searchResult<br>";


Any help would be appreciated
2:11 pm on Dec 8, 2016 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator mack is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 15, 2001
posts:7730
votes: 44


That error would suggest that there were zero rows that met the criteria of the query..

//$query .= " AND displayable ='1'";
that section is commented out, so will not be "read" by php, is there another line earlier that starts with $query?

There is a reason the query is failing, we really need to see the query to determine what it is. Was the script working fine until you inserted today trivia? did you perhaps use punctuation or symbols that you do not generally use ' @ " etc etc? If these characters are not being dealt with in the script they can cause premature line ends and stop queries executing correctly.

Mack.
2:24 pm on Dec 8, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


There have been times when I have put punctuation in my mistake and it threw back an error. As far as how long it has been going on... the error log was quite large I am ashamed to say (looks like it went back to 2014) OOPS

Here is the entire code of the searchfunction.php file

<?php
$operator=$_GET["operator"];
// BEGIN SEARCH FUNCTION //
$searchedby = $keyword;
$searchingby = explode(" ", $keyword); // make keyword query into an array
$searchinALL = explode(" ", $searchin); // make searchin selection into an array
$query = "select * from trivia WHERE ";
$i=0;
while($i<count($searchingby)){
if (count($searchinALL)==1){ // runs if only ONE searchin
if($operator == "OR"){ // creates OR query for ONE searchin
$query .= "$or($searchin like '%$searchingby[$i]%')";
$or = " or ";
}
if($operator == "AND"){ // creates AND query for ONE searchin
$query .= "$and($searchin like '%$searchingby[$i]%')";
$and = " and ";
}
} else { // runs if more then one searchin
$j=0;
while($j<count($searchinALL)) { // creates OR query
if($operator == "OR"){
$query .= "$or($searchinALL[$j] like '%$searchingby[$i]%')";
$or = " or ";
}
$j++;
}
}
$i++;
} // end while
// the AND operator in a multiple searchin needed it's own thing
$j=$i=0;
if($operator == "AND" AND (count($searchinALL)!=1)){
while($j<count($searchinALL)){
$query .= " (";
while($i<count($searchingby)){
$query .= "$and($searchinALL[$j] like '%$searchingby[$i]%')";
$and = " and ";
$i++;
}
$j++;
if($j<count($searchinALL)){
$or = " or";
} else {
$or = "";
}
$query .= ")$or";
$i=0;
$and = "";
}
}
//$query .= " AND displayable ='1'";
$searchResult = mysql_query($query);
$num_rows_searchResult = mysql_numrows($searchResult);
// END SEARCH FUNCTION //
?>
2:25 pm on Dec 8, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


And here is the search.php file if that helps

<?php
include("globals.php");
include("functions.php");
?>
<html>
<head>
<title>Search for Trivia</title>
</head>
<link rel="stylesheet" href="<?php echo $siteURL; ?>styles.css">
<body background="graphics/back.jpg">
<span class="titlefont">Search for Trivia</span>
<p>
<?php
$operator=$_GET["operator"];
$keyword=$_GET["keyword"];
$catID=$_GET["catID"];
$sendDate=$_GET["sendDate"];
$searchDate=$_GET["searchDate"];
$trivID=$_GET["trivID"];
$gotResults=$_GET["gotResults"];
$searchin=$_GET["searchin"];
$search=$_GET["search"];
if($search){ // database search if we are provided a keyword or catID
if($keyword OR $catID){
// get trivia from DB
if($keyword AND !$catID){ // if keyword only
include ("searchFunction.php");
//$searchResult = mysql("$db", "select * from trivia WHERE (question LIKE '%$keyword%') OR (answer LIKE '%$keyword%')");
//echo mysql_error();
//$num_rows_searchResult = mysql_numrows($searchResult);
} else if($catID AND !$keyword){ // if catID only
$searchResult = mysql_query("SELECT * FROM triv_cat WHERE catID = '$catID'");
echo mysql_error();
$num_rows_searchResult = mysql_numrows($searchResult);
} else if ($keyword AND $catID){ // if both keyword and catID
include ("searchFunction.php");
//$searchResult = mysql("$db", "select * from trivia WHERE (question LIKE '%$keyword%') OR (answer LIKE '%$keyword%')");
//echo mysql_error();
//$num_rows_searchResult = mysql_numrows($searchResult);
$row = 0;
while ($row < $num_rows_searchResult): // take our results from the keyword search, and see if they fit into our chosen category
$trivID = mysql_result($searchResult,$row,'trivID');
$result2 = mysql_query("SELECT * FROM triv_cat WHERE trivID='$trivID'");
echo mysql_error();
$num_rows2 = mysql_numrows($result2);
$row2 = 0;
while ($row2 < $num_rows2): // add the current trivID to an array which means it has met both criteria
$catID2 = mysql_result($result2,$row2,'catID');
if($catID == $catID2){
$catResults[] = $trivID;
}
$row2++;
endwhile;
$row++;
endwhile;
$num_rows_searchResult = count($catResults); // get the number of results in our array
}
} else { // if no $keyword or $catID
echo "You must enter something in one of the fields... please try again.";
}
// either use or error on results found above
if($num_rows_searchResult > "0"){
$resultOfSearch = "<span class=errorfont>Your query brought back $num_rows_searchResult results.</span><br>";
$gotResults = "true";
} else if ($num_rows_searchResult == "0"){
$error = "true";
$resultOfSearch = "<span class=errorfont>No results with that search query, please try another.</span><br>";
$gotResults = "";
}
} // end if search
if($searchDate){ // database check if we are provided a date
sendDateCheck($sendDate); // validate date as correct format
if(!$error){ // if above check shows correct format run this
$sendDate = strtotime($sendDate);
$searchResult = mysql_query("SELECT * FROM trivia WHERE sendDate = '$sendDate'");
echo mysql_error();
$num_rows_searchResult = mysql_numrows($searchResult);

if($num_rows_searchResult == "0"){ // couldn't find that date, let's show then the 2 closest to what they provided
$resultOfDateSearch = "<span class=errorfont>No results with that date, please try another. Here are the closest dates to that date.</span><br>";

// this shows the date one earlier then the one they gave us
$dateResult = mysql_query("SELECT * FROM trivia WHERE sendDate <> '$sendDate' LIMIT 5");
$num_rows_dateResult = mysql_numrows($dateResult);
$row3 = 0;
while ($row3 < $num_rows_dateResult):
$trivID2 = mysql_result($dateResult,$row3,'trivID');
$date2 = mysql_result($dateResult,$row3,'sendDate');
$resultOfDateSearch .= "<a href=$PHP_SELF?trivID=$trivID2&gotResults=true>";
$resultOfDateSearch .= date('m/d/Y',$date2);
$resultOfDateSearch .= "</a><br>";

$row3++;
endwhile;

/* this shows the date one later then the one they gave us
$closeDateAbove = mysql("$db", "select * from trivia WHERE sendDate > '$sendDate' LIMIT 1");
$date = mysql_fetch_array($closeDateAbove);
$resultOfDateSearch .= "<a href=$PHP_SELF?trivID=$date[trivID]&gotResults=true>";
$resultOfDateSearch .= date('m/d/Y',$date[sendDate]);
$resultOfDateSearch .= "</a><br>";
*/

} else { // found the date, let's show them the trivia they were looking for
$resultOfDateSearch = "<span class=errorfont>Here is the question for that date.</span><br>";
$gotResults = "true";
}
if($sendDate) $sendDate = date('m/d/Y',$sendDate);
}
}
//echo "<br>num_rows_searchResult = $num_rows_searchResult<br>";
?>

<table border="1" width="600">
<tr>
<td width="50%" valign="top" class="mainfont">
<form action="<?php echo $PHP_SELF; ?>" method="get" name="triviaFind">
<input type="hidden" name="searchin" value="question answer">
<b>Search by Keyword and/or Category</b><br>
You can do a keyword and category search either together or separate. Fill in at least one of the following:<br>
Keyword:&nbsp;<input type="text" name="keyword" value="<?php echo $keyword; ?>" maxlength="20" size="20"><br>
Operator:&nbsp;<select name="operator">
<option>AND
<option>OR
</select><br>
Category:
<?php
// get available category list
$result = mysql_query("SELECT * FROM category ORDER BY name");
echo mysql_error();
$num_rows = mysql_numrows($result);
?>
<select name="catID">
<option>
<?php
$row = 0;
while ($row < $num_rows):
?>

<option value="<?php echo mysql_result($result,$row,'catID'); ?>" <?php if($catID == mysql_result($result,$row,'catID')) echo "selected";?>><?php echo mysql_result($result,$row,'name'); ?>
<?php
$row++;
endwhile;
?>
</select>
<p>
<input type="submit" name="search" value="Search">
</form>
</td>
<td valign="top" class="mainfont">
<form action="<?php echo $PHP_SELF; ?>" method="get" name="triviaFind">
<b>Search by date</b><br>
If you would like to find a trivia question that was sent on a specific date, enter the date below:<br>
<input type="text" name="sendDate" value="<?php echo $sendDate; ?>" maxlength="20" size="20"><br>
<span class="minifont">MUST be formatted MM/DD/YYYY</span><?php echo $sendDateERROR; ?>
<p><input type="submit" name="searchDate" value="Search">
</form>
</td>
</tr>
</table>

<?php echo $resultOfSearch; ?>
<?php echo $resultOfDateSearch; ?>

<?php
if($gotResults){ // if we got some results do this
?>
<table border="1">
<tr>
<td class="headerfont">Question</td>
<td class="headerfont">Answer (highlight to see)</td>
<td class="headerfont">Date Sent</td>
</tr>

<?php
// get trivia from DB
if(($keyword AND !$catID) OR ($resultOfDateSearch)){ // if keyword only, or if sendDate got a result
$row = 0;
while ($row < $num_rows_searchResult):
?>
<tr>
<td class="mainfont"><?php echo mysql_result($searchResult,$row,'question'); echo mysql_error();?></td>
<td class="answerfont"><?php echo mysql_result($searchResult,$row,'answer'); echo mysql_error();?></td>
<td class="mainfont"><?php echo date('m/d/Y',mysql_result($searchResult,$row,'sendDate')); echo mysql_error();?></td>
</tr>
<?php
$row++;
endwhile;
}
if ($catID AND !$keyword){ // if catID only
$row = 0;
while ($row < $num_rows_searchResult):
$trivID = mysql_result($searchResult,$row,'trivID');
displayrow($trivID,$db);
$row++;
endwhile;
}
if ($catID AND $keyword){ // if catID and keyword
$row = 0;
while ($row < $num_rows_searchResult):
$trivID = $catResults[$row];
displayrow($trivID,$db);
$row++;
endwhile;
}
if ($trivID AND !$resultOfSearch){ // if catID and keyword
displayrow($trivID,$db);
}
?>
</table>
<?php
} // end if $gotResults

?>
<center><A HREF="index.html"><img src="graphics/backmain.gif" border="0"></a></center>
</body>
</html>
8:08 pm on Dec 8, 2016 (gmt 0)

Senior Member

WebmasterWorld Senior Member topr8 is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Apr 19, 2002
posts:3377
votes: 41


sorry to be trite but you say yourself: (I am DB Illiterate completely)

and as an example you are running code like:

[b]$trivID=$_GET["trivID"];
$result2 = mysql_query("SELECT * FROM triv_cat WHERE trivID='$trivID'"); [/b]


so you are running db queries from a $_GET value without checking it or cleaning it in any way.
i'm amazed you haven't been hacked, believe me it is waiting to happen and you'll be serving malicious pages to your users.

(unless the searchFunction.php file is checking or cleaning - in which case, i apologise and all is good)

... forget trying to sort out the error you are currently scratching your head about.

learn quick or get someone onboard who will tighten up your code - that would be my priority.
8:13 pm on Dec 8, 2016 (gmt 0)

Moderator from GB 

WebmasterWorld Administrator mack is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:June 15, 2001
posts:7730
votes: 44


I agree, there is a security issue here, You mentioned punctuation has caused issues in the past, it really shouldn't, the SQL query needs to be cleaned up before data is placed in the database, you also need to make sure you Get values are clean before running queries. It's possible for people to analyse your script and possibly place malicious code in there to cause your page to display their content. SQL injection here is a real possibility.

Mack.
9:01 pm on Dec 8, 2016 (gmt 0)

Junior Member

10+ Year Member

joined:Feb 10, 2006
posts: 64
votes: 0


Yes I am DB Illiterate.... I am willing to admit it. I had a friend build the database for me about 6 years ago and I am no longer able to get in touch with him
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members