|
|
Posted by Satyam on 04/20/05 09:45
May I sugest that you assemble the SQL statement in a different way? With
such a big conditional, it will slow down the Sql server.
if ($tit != '') $condition .= "and tit1 like '%$tit%'";
.... and so on for each search field ...
$sql = "Select *, substring(loc1, 1, 3) as loc1 from openart_table where" .
substr($condition,4);
'like' conditions are hard on the server, specially if they start with a '%'
since then no indexes can be used. The server can only go through each and
every single record in the database. If, instead, you avoid the % at the
begining, some index information can be used. You might also want to check
FullText searches
Satyam
"Sugimoto" <sugimoto@momat.go.jp> wrote in message
news:002601c54547$e007a880$d405a8c0@sugimoto...
> Hello,
> I need your help about PHP (ver 4.3.1) and MySQL (ver 3.23) database.
> I am struggling to customise a database (using left command etc), and am
> stuck now.
> Please have a brief look at my scripts below.
>
> What I do like to do is to get first 3 letters from "loc1" column from
> openart_table (MySQL).
> And show the data in the follwoing part...
> <td><? echo $row["loc1"]; ?><br></td>
>
> but also I would like to make hyperlinks to jpg files to images/"the first
> 3
> letters from loc1".jpg
> So it will be something like this...
> <td>
> <a href="/image/<? echo $row["loc1"]; ?>.jpg"><? echo $row["loc1"];
> ?></a><br>
> </td>
>
> But when I tried, "select left (loc1,3) from openart_table where......"
> doesnt work properly.
> I do not want to destroy anything for the rest of the table.
> I need all data from "tit1", "pub1", "id1", and "ref1" in each column.
>
> Could you show me a script example?
> Could you help me, please? Thank you.
>
> ---------------------------------------------PHP script from here
> <html>
> <body>
> <table border="1" bordercolor="black" cellspacing="0" align="center"
> BGCOLOR="#CCFFFF" width="900">
> <tr bgcolor="#ffcc66">
> <td align="center"><b>ID No</b><br></td>
> <td align="center" BGCOLOR="#CC3366"><b>Book Title</b><br></td>
> <td align="center"><b>Publisher</b><br></td>
> <td align="center"><b>ID</b><br></td>
> <td align="center"><b>Reference</b><br></td>
> <td align="center" width="10%"><b>Location</b><br></td>
> </tr>
>
> <?
> mysql_connect(localhost,root,cheers);
> mysql_select_db(openart);
> if($tit == "" &&
> $res == "" &&
> $pub == "" &&
> $date == "" &&
> $id == "" &&
> $ref == "" &&
> $loc == "" &&
> $type == "" &&
> $vol == "" &&
> $fre == "" &&
> $note == "")
> {
> echo 'Type something';
> }
>
> elseif($tit == "%" || $res == "%" || $pub == "%" || $date == "%" || $id ==
> "%" ||
> $ref == "%" || $loc == "%" || $type == "%" || $vol == "%" || $fre ==
> "%" || $note == "%"){
> echo 'Not Valid';
> }
>
> else{
> if($tit == ""){
> $tit = '%';
> }
> if($res == ""){
> $res = '%';
> }
> if($pub == ""){
> $pub = '%';
> }
> if($date == ""){
> $date = '%';
> }
> if($id == ""){
> $id = '%';
> }
> if($ref == ""){
> $ref = '%';
> }
> if($loc == ""){
> $loc = '%';
> }
> if($type == ""){
> $type = '%';
> }
> if($vol == ""){
> $vol = '%';
> }
> if($fre == ""){
> $fre = '%';
> }
> if($note == ""){
> $note = '%';
> }
> $result = mysql_query("select * from openart_table
> where tit1 like '%$tit%'
> and res1 like '%$res%'
> and pub1 like '%$pub%'
> and date1 like '%$date%'
> and id1 like '%$id%'
> and ref1 like '%$ref%'
> and loc1 like '%$loc%'
> and type1 like '%$type%'
> and vol1 like '%$vol%'
> and fre1 like '%$fre%'
> and note1 like '%$note%' order by tit1");
>
> $rows = mysql_num_rows($result);
> echo $rows,"Records Found <p>";
> while($row = mysql_fetch_array($result)){
> ?>
> <tr>
> <td><a href = "openart_detail.php ?iden=<? echo $row["openart_id"] ?>"><?
> echo $row["openart_id"]; ?></a><br></td>
> <td><? echo $row["tit1"]; ?><br></td>
> <td><? echo $row["pub1"]; ?><br></td>
> <td><? echo $row["id1"]; ?><br></td>
> <td><? echo $row["ref1"]; ?><br></td>
> <td><? echo $row["loc1"]; ?><br></td>
> </tr>
> <?
> }
> }
> ?>
> </table>
> </body>
> </html>
>
> ----------------------------------------------------end of script
[Back to original message]
|