Complex (for me) MySQL Query - Driving me nuts!

[JohnnyKennedy]

Okay guys here's hoping you can help.

 

Basically, I have a site with a simple search field which is going to search about five columns in a table of mine those columns being:

 

- bisbn

- bauthor

- btitle

- bpubyear

- bpublisher

 

using a form variable.

 

So far, I have this block of PHP - the '%s' I'm not really sure what these are, they were added by Dreamweaver.  I know I'm going to come across as lazy, but I would be ever so grateful if you could help me out just a little.

 

$colname_sellsearch = "-1";
if (isset($_POST['s'])) {
  $colname_sellsearch = trim($_POST['s']);
}
mysql_select_db($database_scholarexpress, $scholarexpress);
$query_sellsearch = sprintf("SELECT * FROM selltable WHERE (`btitle` LIKE %s) OR (`bisbn` LIKE %s) OR (`bauthor` LIKE %s) OR (`bpubyear` LIKE %s) OR (`bpublisher` LIKE %s) AND `archive`=0 $range ORDER BY $sort", GetSQLValueString("%" . $colname_sellsearch . "%", "text"));
$query_limit_sellsearch = sprintf("%s LIMIT %d, %d", $query_sellsearch, $startRow_sellsearch, $maxRows_sellsearch);
$sellsearch = mysql_query($query_limit_sellsearch, $scholarexpress) or die(mysql_error());
$row_sellsearch = mysql_fetch_assoc($sellsearch);

if (isset($_GET['totalRows_sellsearch'])) {
  $totalRows_sellsearch = $_GET['totalRows_sellsearch'];
} else {
  $all_sellsearch = mysql_query($query_sellsearch);
  $totalRows_sellsearch = mysql_num_rows($all_sellsearch);
}
$totalPages_sellsearch = ceil($totalRows_sellsearch/$maxRows_sellsearch)-1;

 

 

----

 

These are a few variables I've got going on too:

 

$range = "AND `price`<=$rangeh";
$sort = addslashes($_POST['sort']);

 

 

 

[pavankat]

What's your goal. What should happen? What errors are you getting?

 

Also it'll be easier if you could put your code in code blocks. Hit the above button with '#' on it.

[JohnnyKennedy]

Hi, thanks for replying so soon!

 

I'm trying to get a series of results which I've got repeated perfectly (just the query is messed up, my own fault ) from a database - so when the user clicks on one of these results they are taken to the dynamic book page.

 

So, to simplify things i'm having the user search through by using a simple one-field form, which in turn searches the five columns mentioned with that one search string..

 

This is the error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 20' at line 1

 

Hope this helps - thanks again buddy!

 

[kickstart]

Hi

 

The %s in the string is a place holder, and will be replaced by the 2nd, 3rd, 4th, etc arguments to sprintf. However you are only supplying one.

 

Also don't know what the values of $sort or $range are.

 

You are also not cleaning the input at all. So are wide open to SQL injection (depending on what GetSQLValueString does).

 

Something like this is wanted (assuming $sort is sanitised before use):-

 

$query_sellsearch = sprintf("SELECT * FROM selltable WHERE ((`btitle` LIKE '%1$s') OR (`bisbn` LIKE '%2$s') OR (`bauthor` LIKE '%3$s') OR (`bpubyear` LIKE '%4$s') OR (`bpublisher` LIKE '%5$s')) AND `archive`=0 ORDER BY %6$s LIMIT %7$d, %8$d ", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", $sort, $startRow_sellsearch, $maxRows_sellsearch);

 

All the best

 

Keith

[JohnnyKennedy]

Keith,

 

Thanks for replying!  Unfortunately, that code isn't really working for me at the moment I'm afraid -- it's not return any rows at all..

 

$sort is sanatized - and is basically determined by a <select> field where the values are btitle ASC or btitle DESC etc. etc.

 

$range on the other hand is in an If() statement and if it's present, it will determine the highest price.. 

For example:  where high is the input field,

 

$rangeh = addslashes($_POST['high']);
if ($rangeh > 0) {

$range = "AND `price`<=$rangeh"; }
 else 
{ $range = "";
}

 

 

 

Basically, this is what I'm looking at - which isn't returning any results..

 

$maxRows_sellsearch = 20;
$pageNum_sellsearch = 0;
if (isset($_GET['pageNum_sellsearch'])) {
  $pageNum_sellsearch = $_GET['pageNum_sellsearch'];
}
$startRow_sellsearch = $pageNum_sellsearch * $maxRows_sellsearch;

$colname_sellsearch = "-1";
if (isset($_POST['s'])) {
  $colname_sellsearch = trim($_POST['s']);
}
mysql_select_db($database_scholarexpress, $scholarexpress);

$query_sellsearch = sprintf("SELECT * FROM selltable WHERE ((`btitle` LIKE '%2$s') OR (`bisbn` LIKE '%2$s') OR (`bauthor` LIKE '%3$s') OR (`bpubyear` LIKE '%4$s') OR (`bpublisher` LIKE '%5$s')) AND `archive`=0 ", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", "%".mysql_real_escape_string($colname_sellsearch)."%", $sort, $startRow_sellsearch, $maxRows_sellsearch);

$query_limit_sellsearch = sprintf("%s LIMIT %d, %d", $query_sellsearch, $startRow_sellsearch, $maxRows_sellsearch);
$sellsearch = mysql_query($query_limit_sellsearch, $scholarexpress) or die(mysql_error());
$row_sellsearch = mysql_fetch_assoc($sellsearch);

if (isset($_GET['totalRows_sellsearch'])) {
  $totalRows_sellsearch = $_GET['totalRows_sellsearch'];
} else {
  $all_sellsearch = mysql_query($query_sellsearch);
  $totalRows_sellsearch = mysql_num_rows($all_sellsearch);
}
$totalPages_sellsearch = ceil($totalRows_sellsearch/$maxRows_sellsearch)-1;

 

 

Is there any way to remove unnecessary code - or ultimately - get rid of those annoying %s that DW has put in?

 

---

 

I appreciate you taking the time to help me, you are literally a life-saver!

 

Warm Regards,

 

Jonathan

[kickstart]

Hi

 

Without a sample of you data and table layout I can't really test it.

 

The %s bits are placeholders, and sprintf replaces them with the variables.

 

You can remove them and sprintf:-

 

<?php

$colname_sellsearch = "-1";
if (isset($_POST['s'])) {
  $colname_sellsearch = trim($_POST['s']);
}
mysql_select_db($database_scholarexpress, $scholarexpress);
$SearchItem = "%".mysql_real_escape_string($colname_sellsearch)."%";
$query_sellsearch = "SELECT * FROM selltable WHERE (`btitle` LIKE '$SearchItem') OR (`bisbn` LIKE '$SearchItem') OR (`bauthor` LIKE '$SearchItem') OR (`bpubyear` LIKE '$SearchItem') OR (`bpublisher` LIKE '$SearchItem') AND `archive`=0 ORDER BY $sort LIMIT $startRow_sellsearch, $maxRows_sellsearch ";
$sellsearch = mysql_query($SearchItem, $scholarexpress) or die(mysql_error());
$row_sellsearch = mysql_fetch_assoc($sellsearch);

if (isset($_GET['totalRows_sellsearch'])) {
  $totalRows_sellsearch = $_GET['totalRows_sellsearch'];
} else {
  $all_sellsearch = mysql_query($query_sellsearch);
  $totalRows_sellsearch = mysql_num_rows($all_sellsearch);
}
$totalPages_sellsearch = ceil($totalRows_sellsearch/$maxRows_sellsearch)-1; 

?>

 

All the best

 

Keith

[JohnnyKennedy]

Thanks again Keith!

 

Do you know why I would be getting this error using the code you provided for me:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%isearchedforthis%' at line 1

 

then it obviously dies.

 

Regards,

 

Jonathan

 

[JohnnyKennedy]

Never mind buddy, fixed it it was on the $query_sellsearch line.

 

Thank you so much for your help - it's working great now.  I honestly love you right now!

 

Have a great day!

 

VERY Warm Regards,

 

Jonathan