Re: PHP script problem (with MySQL)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx> wrote in message 
news:002601c54547$e007a880$d405a8c0@xxxxxxxxxxx
> 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 

-- 
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [Apache Users]     [PHP on Windows]     [Kernel Newbies]     [PHP Install]     [PHP Classes]     [Pear]     [Postgresql]     [Postgresql PHP]     [PHP on Windows]     [PHP Database Programming]     [PHP SOAP]

  Powered by Linux