Thivya Posted August 14, 2007 Share Posted August 14, 2007 hello there, I have problem in doing search function using php and SQl.The question is i need to retrieve data based on user input form the database where the user input(user will insert year,example,2007).I want the sql command to search in the table whether any dates(under Date field) has the input 2007 or not.If yes it should display all the data for the year 2007... In my interface i named textbox value as year, My sql command like this... $sql = "SELECT * FROM Temper"; $sql .= " WHERE ( Temper.Date LIKE '{$_POST['year']}')"; it didnt work... i also try like this; $sql = "SELECT * FROM Temper"; $sql .= " WHERE ( Temper.Date HAVING '{$_POST['year']}')"; It never work also..My table name is Temper,the fields are Date,PH,Temperature,Location,Day I hope any of you can help me... thakx... Quote Link to comment Share on other sites More sharing options...
gurroa Posted August 14, 2007 Share Posted August 14, 2007 $sql = "SELECT * FROM Temper WHERE Date >= '{$_POST['year']}-01-01' and Date <= '{$_POST['year']}-12-31'" Quote Link to comment Share on other sites More sharing options...
Illusion Posted August 14, 2007 Share Posted August 14, 2007 SELECT * FROM Temper WHERE YEAR(`Date `)='2007'; Date is a keyword , we can't use it as a column name. Quote Link to comment Share on other sites More sharing options...
Thivya Posted August 14, 2007 Author Share Posted August 14, 2007 Oh ok...i will try to rename the column and use the commands as you were suggesting...thankx for all the replies... I have to try to review my codes again,i guess.Thakx Quote Link to comment Share on other sites More sharing options...
SnowControl Posted August 14, 2007 Share Posted August 14, 2007 I use it as a column name without any problems. You just have to quote `date` in the query. Quote Link to comment Share on other sites More sharing options...
Thivya Posted August 15, 2007 Author Share Posted August 15, 2007 The given sql commands still can't retrieve data from the table... For this command, SELECT * FROM Temper WHERE YEAR(`Date `)='2007'; It looks like it will only retrieve data for the year 2007 only...I want the result based on the user what user key in the textbox( i named the text box as year) ...The year will be in date(10.10.2007,...).So the query must be able to match the user input in table in Date field... In this command, $sql = "SELECT * FROM Temper WHERE Date >= '{$_POST['year']}-01-01' and Date <= '{$_POST['year']}-12-31'" The command still fail to display the data..But i think it looks like as i wanted...Can you please try to review this command and help me... Thanks for all the reply... Quote Link to comment Share on other sites More sharing options...
gurroa Posted August 15, 2007 Share Posted August 15, 2007 You should mention... $year = trim($_POST['year']); $datefrom = ''; if (!empty($year)) { $ardate = array($year); if (strpos($year, '.') !== false) $ardate = Explode('.', $year); else if (strpos($year, '-') !== false) $ardate = Explode('-', $year); $c = count($ardate); if ($c == 3) { $datefrom = sprintf("%04d-%02d-%02d", trim($ardate[2]), trim($ardate[1]), trim($ardate[0]) ); $dateto = $datefrom; } elseif ($c == 2) { $datefrom = sprintf('%04d-%02d-01', trim($ardate[1]), trim($ardate[0]) ); $dateto = Date("Y-m-d", strtotime("+1 month -1 day", strtotime(sprintf('%04d-%02d-01', trim($ardate[1]), trim($ardate[0])) )) ); } elseif (is_integer($year)) { $datefrom = sprintf('%04d-01-01', $year); $dateto = sprintf('%04d-12-31', $year); } } if (!empty($datefrom)) { $sql = "SELECT * FROM Temper WHERE Date >= '{$datefrom}' and Date <= '{$dateto}'"; .... } Quote Link to comment Share on other sites More sharing options...
Thivya Posted August 16, 2007 Author Share Posted August 16, 2007 hi, $sql = "SELECT * FROM Temper WHERE Date >= '{$_POST['year']}-01-01' and Date <= '{$_POST['year']}-12-31'" Actually the above sql command work!It display the output as i expect...i wrongly name my textbox,thus i was unable to get the right output...So after i correct it i[tt][/tt]display the output as i expected... Thanks a lot.... I have a another question ,if you dont mind ,can youi please help me again...i already have my table to store image,where i have table named(message),fields are(imgdata[longblob]),pid(increment by default),...my database named as "wsn"... Now i was able to retieve other values from the table except the image..i had tried all the coding that were discussed in the forum...But is still not working... ??? My output should be like this, John date posted Message ---------------- Ally Dte posted Message ...................... until all the data from that table will be displayed ....my coding are as belows......That codes i used for the searching based on keyword(just ignore that part)..I don't know...How to display the image next to the person name.... Hope to get a reply...thankx a lot... <? //set up database and table names $db_name = "wsn"; $table_name = "message"; //connect to MySQL and select database to use $connection = @mysql_connect("localhost", "123", "thivya") or die(mysql_error()); $db = @mysql_select_db($db_name, $connection);// or $page_name="fo.php"; if(!isset($start)) { // This variable is set to zero for the first page $start = 0; } $eu = $start; $limit = 3; // No of records to be shown per page. $current = $eu + $limit; $back = $eu - $limit; $next = $eu + $limit; $query2 = " SELECT * FROM $table_name ORDER BY '$_POST[keyword]' "; $result2 = mysql_query($query2) or die(mysql_error()); $nume = mysql_num_rows($result2); if(isset($_POST['keyword'])) { $search = $_POST['keyword']; } $keywords = explode(" ", $search); if(!isset($start)) { // This variable is set to zero for the first page $start = 0; } $query = " SELECT * FROM $table_name ORDER BY userstatus ASC limit $eu, $limit"; for ($i=1; $i<count($keywords); $i++) { $query = $query." AND user_name LIKE '%".$keywords[$i]."%'"; } $result= mysql_query($query) or die(mysql_error()); $num=@mysql_num_rows($result); if($num<1) { $mesg = "SORRY!!! NO RECORDS BY THIS NAME<P>PLEASE TRY ANOTHER"; header( "Location: http://localhost/fyp/message2.php?mesg=$mesg"); exit; } while($row = mysql_fetch_array($result)) { $display_block .= " <tr><td>---------------------------------------- <b></b><font color=blue><b><p><b>User/Email:</b><i>$row[usrname_email]</i><i> $row[userID]</i></b></font>----------------------------------------<p> <b>User Status:</b><i>$row[userstatus]</i><p><b>Date:</b><i>$row[date]</i><p><b>Full Message:</b><i>$row[full_message]</i> </td> </tr>"; }?> <html> <head> .......................... Quote Link to comment Share on other sites More sharing options...
gurroa Posted August 16, 2007 Share Posted August 16, 2007 To display image you will have to create new php script that will accept pid and output only the image. This new script vary from way you are saving image data into the dbase. I would save images as base64_encode()d raw data. And as long as you store imgdata into your message table don't select this field when you don't need it. Otherwise imgdata is retrieved from your db too. SELECT pid, usrname_email, userID, userstatus, full_message FROM $table_name ORDER BY userstatus ASC <?php //viewimage.php Header("Pragma: no-cache"); Header("Cache-Control: no-cache"); Header("Expires: ".GMDate("D, d M Y H:i:s")." GMT"); if (isset($_GET['pid']) && is_int($_GET['pid'])) { mysql_connect(.... // connection $res = mysql_query("select imgdata from message where pid = ".$_GET['pid']); if ($res) { $row = mysql_fetch_assoc($res); // This is only if you are storing jpeg images Header("Content-type: image/jpeg"); Header("Content-Disposition: inline; filename=img".$_GET['pid'].".jpeg"); echo base64_decode($row['imgdata']); } } ?> To put such image into your messages script while($row = mysql_fetch_array($result)) { $display_block .= " <tr><td>---------------------------------------- <font color=blue><p>User/Email:".$row['usrname_email']." ".$row['userID']."</font> <img src=\"viewimage.php?pid=".$row['pid']."\" align=left ...> ----------------------------------------<p> User Status:$row['userstatus']<p>Date:$row[date]<p>Full Message:$row['full_message'] </td> </tr>"; } Quote Link to comment Share on other sites More sharing options...
Thivya Posted August 17, 2007 Author Share Posted August 17, 2007 hi, I have tried to do as the given code...but only the icon for the picture is appearing,the picture still fail to be displayed...i think my codes got some errors somewhere...So i am giving here the codes that i use,which you provide yesterday...could you please go through the codes and see what is the problem...pls... <? //set up database and table names $db_name = "wsn"; $table_name = "message"; //connect to MySQL and select database to use $connection = @mysql_connect("localhost", "123", "Thivya") or die(mysql_error()); $db = @mysql_select_db($db_name, $connection);// or $page_name="Forum.php"; if(!isset($start)) { // This variable is set to zero for the first page $start = 0; } $eu = $start; $limit = 3; // No of records to be shown per page. $current = $eu + $limit; $back = $eu - $limit; $next = $eu + $limit; $query = " SELECT pid, usrname_email, userID, userstatus, full_message FROM $table_name ORDER BY userstatus "; $result = mysql_query($query) or die(mysql_error()); $num = mysql_num_rows($result); if($num<1) { $mesg = "SORRY!!! NO RECORDS BY THIS NAME<P>PLEASE TRY ANOTHER"; header( "Location: http://localhost/fyp/message2.php?mesg=$mesg"); exit; } while($row = mysql_fetch_array($result)) { $display_block .= " <tr><td>---------------------------------------- <font color=blue><p>User/Email:".$row['username']." ".$row['userID']."</font> <img src=\"viewimage.php?pid=".$row['pid']."\" align=left ...> ----------------------------------------<p> User Status:$row['userstatus']<p>Date:$row[date]<p>Full Message:$row['full_message'] </td> </tr>"; }?> <html> <head> <title>PEDVWSN...</title> <link rel="stylesheet" type="text/css" href="style.css"> <style type="text/css"> <!-- .style1 {font-size: 12px} --> </style> </head> <body leftmargin=0 topmargin=0 marginheight="0" marginwidth="0" bgcolor="#01557C"> <table width="759" border="0" cellspacing="0" cellpadding="0" align="center"> <tr> <td width="256"><img src="images/main01.jpg" width="256" height="41"></td> <td background="images/fon01.gif"> <table width="503" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="270"><a href="#"><img src="images/but_home.gif" width="74" height="41" border="0"></a><img src="images/separator.gif" width="2" height="41"><a href="#"><img src="images/but_site_map.gif" width="94" height="41" border="0"></a><img src="images/separator.gif" width="2" height="41"><a href="#"><img src="images/but_contacts.gif" width="96" height="41" border="0"></a><img src="images/separator.gif" width="2" height="41"></td> <td width="233"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="10%"> <p></p> </td> <form method="POST" action="Search_ResearcherMain.php"> <td class="title"><b><font color="#000000">SEARCH</font></b> <input type="Text" name="keyword" value="" size="10" align="absmiddle"> <input type="Image" src="images/b_go3.gif" width="22" height="28" alt="" border="0" hspace="10" align="absmiddle" name="Search"> </td> </form> </td> <td width="10%"></td> </tr> </table> </td> </tr> </table> </td> </tr> <tr> <td height="157"><img src="images/PESWSN.bmp" width="256" height="153"></td> <td width="503"><object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=6,0,29,0" width="503" height="157"> <param name="movie" value="images/FLASH.swf"> <param name=quality value=high> <embed src="images/FLASH.swf" quality=high pluginspage="http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash" type="application/x-shockwave-flash" width="503" height="157"></embed> </object></td> </tr> </table> <table border="0" cellpadding="0" cellspacing="0" width="759" align="center" background="images/fon_menu.gif"> <tr> <td><img src="images/menu01.gif" width="3" height="42" alt="" border="0"></td> <td> <table border="0" cellpadding="0" cellspacing="0"> <tr> <td><p class="menu01"><a href="" class="style1">HOME</a></p></td> <td><img src="images/e_menu.gif" alt="" width="24" height="42" border="0"></td> <td><p align="center" class="menu01"><a href="UpdateResearcherProfile.php">Edit Profile</a></p></td> <td><img src="images/e_menu.gif" alt="" width="24" height="42" border="0"></td> <td><p align="center" class="menu01"><a href="">Update Data</a></p></td> <td><img src="images/e_menu.gif" alt="" width="24" height="42" border="0"></td> <td><p align="center" class="menu01"><a href="Researcher_view_message.php">View Comment</a>s</p></td> <td><img src="images/e_menu.gif" alt="" width="24" height="42" border="0"></td> <td><p align="center" class="menu01"><a href="log_out.php">Sign Out</a></p></td> <td><img src="images/e_menu.gif" alt="" width="24" height="42" border="0"></td> </tr> </table> </td> <td align="right"><img src="images/menu02.gif" width="3" height="42" alt="" border="0"></td> </tr> </table> <div align="center"><img src="images/main01.gif" width="759" height="5" alt="" border="0"></div> <table border="0" cellpadding="0" cellspacing="0" width="759" align="center"> <tr valign="top"> <td background="images/fon_left.gif"><img src="images/fon_left.gif" width="3" height="13" alt="" border="0"></td> <td width="753" bgcolor="#FFFFFF"> <p class="px5"> <table border="0" cellpadding="12" cellspacing="0" width="100%"> <tr valign="top"> <td align="right"> <!-- left --> <!-- /left --></td> <td> <!-- right --> <table width="571" border="0" cellpadding="0" cellspacing="0"> <tr> <td colspan="3" bgcolor="#CCCCCC"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td> </tr> <tr> <td width="97" rowspan="3" bgcolor="white"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td> <td bgcolor="white" height="4"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td> <td width="4" rowspan="3" bgcolor="white"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td> </tr> <font size="+2" style="color:696969" face="Tahoma">WELCOME <? echo $name ?>... </font> <tr> <td width="357"> <table cellspacing=3 cellpadding=3 > <? echo "$display_block";?> <?php echo "<table align = 'center' width='50%'><tr><td align='left' width='30%'>"; //// if our variable $back is equal to 0 or more then only we will display the link to move back if($back >= 0) { print "<a href='$page_name?start=$back'> <font face='Verdana' size='+1'>PREV</font></a>"; } // Let us display the page links at center. We will not display the current page as a link echo "</td><td align=center width='30%'>"; $i=0; $l=1; for($i=0; $i < $num; $i=$i+$limit) { if($i <> $eu) { echo " <a href='$page_name?start=$i'> <font face='Verdana' size='+1'>$l</font></a> "; } else { echo "<font face='Verdana' size='+2' color=red>$l</font>"; /// Current page is not displayed as link and given font color red } $l=$l+1; } echo "</td><td align='right' width='30%'>"; // If we are not in the last page then Next link will be displayed. Here we check that if($current < $num) { print "<a href='$page_name?start=$next'> <font face='Verdana' size='+1'>NEXT</font></a>"; } echo "</td></tr></table>"; ?> <p class="left" style="color: #FF6600;"> </p> <p class="left" style="color: #FF6600;"> </p> <p class="left"> </p> <p class="left"> </p> <div align="center"> </div> <table border="0" cellpadding="5" cellspacing="0"> <tr valign="top"> <td align="right" width="170"><p class="px5"></td> <td> <p class="left" style="color: #FF6600;"> </p> <p class="left" style="color: #FF6600;"> </p><p class="left"> </p> <p class="left"> </p> <form method="post" action="ResearcherMain.php"><input type="submit" name="submit" value=" Next Search"></a> </td> </tr> </table> </td> </tr> <tr> <td background="images/t_fon.gif" height="28"><img src="images/px1.gif" width="1" height="1" alt="" border="0"></td> </tr> <tr> <td colspan="3" align="right"><img src="images/t_bot.gif" width="229" height="9" alt="" border="0"></td> </tr> </table> <!-- /right --> </td> </tr> </table> <br> </td> <td background="images/fon_right.gif"><img src="images/fon_right.gif" width="3" height="10" alt="" border="0"></td> </tr> </table> <table border="0" cellpadding="0" cellspacing="0" width="759" align="center"> <tr> <td colspan="2" background="images/fon_bot.gif" height="42" align="right"> <table border="0" cellpadding="0" cellspacing="0" width="510" background=""> <tr> <td><p class="menu02"> </p></td> <td><p class="menu02"> </p></td> <td><p class="menu02"> </p></td> <td><p class="menu02"> </p></td> <td><p class="menu02"> </p></td> <td> </td> <td><p class="menu02"> </p></td> </tr> </table> </td> </tr> <tr align="center"> <td width="270" height="80"> </td> <td width="490"><p align="center" style="color: #FFFFFF;"> </p></td> </tr></table> </body></html> and this is the viewimage.php codes.... <?php //viewimage.php Header("Pragma: no-cache"); Header("Cache-Control: no-cache"); Header("Expires: ".GMDate("D, d M Y H:i:s")." GMT"); if (isset($_GET['pid']) && is_int($_GET['pid'])) { mysql_connect("localhost", "123", "Thivya") or die(mysql_error()); $db = @mysql_select_db($db_name, $connection);// or $res = mysql_query("select imgdata from message where pid = ".$_GET['pid']); if ($res) { $row = mysql_fetch_assoc($res); // This is only if you are storing jpeg images Header("Content-type: image/jpeg"); Header("Content-Disposition: inline; filename=images".$_GET['pid'].".jpeg"); echo base64_decode($row['imgdata']); } } ?> I want to know,whether i can use the lonblob as data type for the image or not?? Hope to get a reply....sorry for keep asking the same question again....Thankx... Quote Link to comment Share on other sites More sharing options...
gurroa Posted August 17, 2007 Share Posted August 17, 2007 And how do you store images into the database? If you don't store them base64 encoded than you can't retrieve them as base64 decode. Quote Link to comment Share on other sites More sharing options...
gurroa Posted August 17, 2007 Share Posted August 17, 2007 Try echo $row['imgdata']; Quote Link to comment Share on other sites More sharing options...
Thivya Posted August 27, 2007 Author Share Posted August 27, 2007 Thanks..it works... Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.