totally newbie need help with searching from Mysql database

if13121 · 11-01-2004, 02:08 AM

i have database ICT with table news and consist off id,title, abstraksi(news synopsys),content, datetime and photo and publish (news will be published or not). I want to divide the search result 10 result per page but it doesnt work. whats wrong with this program.. How to make search option which selected by user search by title. I have datetime format how to print only date or only news time from database. If i change the field datetime with field date and field time. How to sort lastest news .
<html>
<head><title></title></head>
<body>

<form action = search_news.php method = post>
</table>
<tr> <td >Search</td>
<td><input type = text name = keyword size = 20 ></td>
<td>

<select name=id class=textfield>";
<?php
echo "<option value = ".$arr_topic[$i][id];
if ($id == $arr_topic[$i][id]) {
echo " selected";
}
echo ">".$arr_topic[$i][kategori]."</option>";
}
echo "</select>

?>
</td>
<td><input type = submit name = submit value = Go ><td>
</tr>
</table>
</form>

<?php
$user = "root";
$host = "localhost";
$pass = "";
$connection = mysql_connect($host,$user,$pass);
$query = "select * from news where title like '%keyword%'
or abstraksi like '%keyword%'and publish == 'Y'";
$nb_result = mysql_query($query);
$nb_row_found = mysql_num_rows($nb_result);
$rowperpage = 10; //nb news per page
if (empty($offset)){
$offset = 0;
}
if ($nb_row_found==0){
echo "<b>$keyword</b> doesn't matched any news";
exit;
}
else{
echo "<b>$keyword</b><br> found ".$nb_row_found."";
}
//get result
$result = mysql_query("select * from news where title like '%$skeyword%'
or abstraksi like '%$keyword%'order by id limit $offset,$rowperpage");
echo "<table align = center><h2>News found : </h2>";
echo "<table border = 0 >";
while($row_result = mysql_fetch_arrray($result))
{ echo "<tr>";
echo "<td>";
echo "<a href =\"$self?id=$row_result[id]\ >".$row_result[title]."</a>";
echo $row_result[abstraksi];//abstraksi
echo "<td>";
echo "<tr><td align = right>";
echo "</td></tr>";
}
echo "</table>";
echo "<div align = center>";
if ($offset != 0){
$prevoffset = $offset-$rowperpage;
echo "<a href = $PHP_SELF?offset= $prevoffset>Prev</a><nobr>";
}
//count pages
$max_page = ceil($nb_row_found/$rowperpage);
for ($i = 1;$i <= $maxpage;$i++){
$newoffset = $rowperpage * ($i -1);
if ($offset != $newoffset){//print link to page
echo "<a href = $PHP_SELF?offset = $newoffset>$i</a></nobr>";
}
else{//print without link
echo " ".$i." ";
}
if ((!($offset/$rowperpage) + 1 == $max_page)&& $maxpage != 1 ){
$newoffset = $offset + $rowperpage;
echo "<a href = $PHP_SELF?offset = $newoffset>Next</a><nobr>";
echo "</div>";
}

}

?>

</body>
</html>

idx · 11-01-2004, 04:00 AM

Quote:

How to sort lastest news .

Try adding an ORDER BY statement and sort by either the datetime field or maybe id.

eg:

PHP Code:


			
$query = <<<EOQ

SELECT *

FROM news

WHERE ((publish = 'Y') AND 

(title like '%keyword%' OR abstraksi like '%keyword%'))



ORDER BY datetime DESC

EOQ;

You can also limit the number of results by using the LIMIT statement in your query.

-r

if13121 · 11-01-2004, 12:57 PM

sory there is some mistake inmy code. Here is the code:

PHP Code:


			
<html>

<head><title></title></head>

<body>



<form action = search_news.php method = post>

//this link doesn't work. I want, i can search from the search page. Pls help.. :confused: 

</table>

<tr> <td >Search</td>

<td><input type = text name = keyword size = 20 ></td>

<td>

</td>

<td><input type = submit name = submit value = Go ><td>

</tr>

</table>

</form>



<?php

$user = "root";

$host = "localhost";

$pass = ""; 

$connection = mysql_connect($host,$user,$pass);

$query = "select * from news where title like '%$keyword%'

or abstraksi like '%$keyword%'and publish == 'Y'";

$nb_result = mysql_query($query);

$nb_row_found = mysql_num_rows($nb_result);

$rowperpage = 10; //nb news per page 

if (empty($offset)){

$offset = 0;

} 

if ($nb_row_found==0){

echo "<b>$keyword</b> doesn't matched any news";

exit;

}

else{

echo "<b>$keyword</b><br> found ".$nb_row_found."";

}

//get result

$result = mysql_query("select * from news where title like '%$skeyword%'

or abstraksi like '%$keyword%'order by id limit $offset,$rowperpage");

echo "<table align = center><h2>News found : </h2>";

echo "<table border = 0 >";

while($row_result = mysql_fetch_array($result))

{ echo "<tr>";

echo "<td>";

echo "<a href = view($row_result[id]) >".$row_result[title]."</a>";

/* i want to make a function to view news content which id = $row_result[id] can i call it like this ???  :confused: */ 

echo $row_result[abstraksi];//abstraksi

echo "<td>";

echo "<tr><td align = right>";

echo "</td></tr>";

}

echo "</table>";

echo "<div align = center>";

if ($offset != 0){

$prevoffset = $offset-$rowperpage;

echo "<a href = $PHP_SELF?offset= $prevoffset>Prev</a><nobr>";

}

//count pages

/*i have get the result of the search but if the result more than 1 page i still don't get link to next page result whats wrong here?? pls help :confused: */

$max_page = ceil($nb_row_found/$rowperpage);

for ($i = 1;$i <= $maxpage;$i++){

$newoffset = $rowperpage * ($i -1);

if ($offset != $newoffset){//print link to page

echo "<a href = $PHP_SELF?offset = $newoffset>$i</a></nobr>";

}

else{//print without link

echo " ".$i." ";

}

if ((!($offset/$rowperpage) + 1 == $max_page)&& $maxpage != 1 ){

$newoffset = $offset + $rowperpage;

echo "<a href = $PHP_SELF?offset = $newoffset>Next</a><nobr>";

echo "</div>";

}



}



?>



</body>

</html>

// how to make an option search news by title or by content?  :( 

//thank you

in my database i have a field with datetime type how to print just the time or just the date from that type :confused:

idx · 11-02-2004, 04:34 AM

You may need to surround the action/method in quotes.

Code:

<form action="search_news.php" method="POST">

Quote:

in my database i have a field with datetime type how to print just the time or just the date from that type

I can't remember how the datetime type is formatted, but you're either going to have to use internal mysql functions (if there are any) or use PHP string functions to extract the portion of the datetime then display as needed.

http://dev.mysql.com/doc/mysql/en/DATETIME.html

So if the format is YYYYMMDDHHMMSS:

PHP Code:


			
$dt = substr($row_result["datetime_fieldname"], 0, 8);

// now $dt is "YYYYMMDD"

$tm = substr($row_result["datetime_fieldname"], 8);

// $tm is HHMMSS