Jump to content


 


Register a free account to unlock additional features at BleepingComputer.com
Welcome to BleepingComputer, a free community where people like yourself come together to discuss and learn how to use their computers. Using the site is easy and fun. As a guest, you can browse and view the various discussions in the forums, but can not create a new topic or reply to an existing one unless you are logged in. Other benefits of registering an account are subscribing to topics and forums, creating a blog, and having no ads shown anywhere on the site.


Click here to Register a free account now! or read our Welcome Guide to learn how to use this site.

Photo

Some help on a SQL query


  • Please log in to reply
4 replies to this topic

#1 lanzd

lanzd

  • Members
  • 41 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:03:24 AM

Posted 06 December 2011 - 08:35 PM

I basically have a select box with values ranging from a-z in increments such as a-g, h-n, o-z and need to search a table for all records whos last name starts with one of the letters within the range.

The way I thought of doing it was like this(psudocode):

CREATE VIEW 'view1' AS
SELECT * FROM table1 ORDER BY lastname ASC;

and then

SELECT * FROM view1
WHERE lastname BETWEEN 'a%' AND 'g%'

I would be integrating a strtolower type of function and ways to get the letters I need from the select box.

But I read that depending on you version of mysql the BETWEEN behaves differently. I don't know my version of mysql, I don't have access at the moment to do a <?php echo phpinfo; ?>. But is this generally the way everyone does this? There has to be a better way that isn't dependent on what version of mysql you have. Also I assume there is probably a way to combine the two statements I currently have, I don't know how though, If there is a way to combine them can someone give me a way to combine the two statements?

Thank You.

Edited by lanzd, 06 December 2011 - 08:42 PM.


BC AdBot (Login to Remove)

 


#2 groovicus

groovicus

  • Security Colleague
  • 9,963 posts
  • OFFLINE
  •  
  • Gender:Male
  • Location:Centerville, SD
  • Local time:02:24 AM

Posted 06 December 2011 - 08:54 PM

As far as I know, you can't use wildcards in a 'between' operator. Meaning "BETWEEN 'a%' AND 'g%'" probably will not work. I am not sure though because I have never tried. At any rate, your approach should work; I can think of a number of different versions that would work ok also. Unless you are working with tens of millions of records, your approach won't matter much.

If you use standard sql, then you will not have to worry about what version of mysql you are using. So when looking for code samples, search for sql samples, not mysql examples.

#3 lanzd

lanzd
  • Topic Starter

  • Members
  • 41 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:03:24 AM

Posted 06 December 2011 - 08:58 PM

Okay, thank you. So I'll plan on doing what I stated above without the wildcards tommarrow (I'll try it with them also and let you know how it turns out). And I did not know I could use standard sql and it would work in mysql regardless of version. Thats good to know, thank you. :lol:

Edited by lanzd, 06 December 2011 - 09:00 PM.


#4 lanzd

lanzd
  • Topic Starter

  • Members
  • 41 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:03:24 AM

Posted 07 December 2011 - 01:07 PM

I have tried my code and it seems to be working but it is throwing errors.

I'm in a LAMP environment, this is my code.

<?php
mysql_select_db($database_conn1, $conn1);

//DROPING VIEW
$query_rsDropView = "DROP VIEW attorneyOrder;";
echo $query_rsDropView . "/////";  //this code outputs nothing, not even the "/////" part.
$rsDropView = mysql_query($query_rsDropView, $conn1) or die(mysql_error());
echo $rsDropView . "/////";  //this code also output nothing, not even the "/////" part.
$row_rsDropView = mysql_fetch_assoc($rsDropView);        //line 222
$totalRows_rsDropView = mysql_num_rows($rsDropView);     //line 223

//CREATING VIEW
$query_rsView = "CREATE VIEW attorneyOrder AS SELECT * FROM LewisJohsAttorneys ORDER BY lname ASC;";
$rsView = mysql_query($query_rsView, $conn1) or die(mysql_error());
$row_rsView = mysql_fetch_assoc($rsView);               //line 228
$totalRows_rsView = mysql_num_rows($rsView);            //line 229

//GETTING VALUES FOR SELECT ON VIEW
$urlStart_rsName = "NULL";
if (isset($_GET['start'])) {
$urlStart_rsName = $_GET['start'];
}
$urlEnd_rsName = "NULL";
if (isset($_GET['end'])) {
$urlEnd_rsName = $_GET['end'];
}

//SELECTING DATA FROM VIEW
$query_rsName = sprintf("SELECT * FROM attorneyOrder WHERE attorneyOrder.lname BETWEEN %s AND %s;", GetSQLValueString($urlStart_rsName, "text"),GetSQLValueString($urlEnd_rsName, "text"));
$rsSearch = mysql_query($query_rsName, $conn1) or die(mysql_error());
$totalRows_rsName = mysql_num_rows($rsName);       //line 249

//OUTPUTING DATA
while($rsSearch = mysql_fetch_assoc($rsSearch)){  //line 262
	//do some stuff here
}

?>

The errors I'm getting are:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 222

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 223

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 228

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 229

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 249

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /var/www/lewisjohs.com/attorneys-search.php on line 262

I'm assuming its all stemming from the same problem and that is why each successive query is returning an invalid value since they all build off of the prior ones output.

But, I'm still getting the correct output. I could just suppress the errors but I want to know why these errors are coming up. After searching online a common cause was not specifying the database, but that's the first thing I'm doing with this line
mysql_select_db($database_conn1, $conn1);

I tried outputing the return result of the drop view query but nothing gets output to the screen. I even closed the php tag and used html text then opened the php tags and that html text doesn't get output to the screen anywhere.

#5 lanzd

lanzd
  • Topic Starter

  • Members
  • 41 posts
  • OFFLINE
  •  
  • Gender:Male
  • Local time:03:24 AM

Posted 07 December 2011 - 04:47 PM

Just as an update, I have everything working properly, dropping the table, re-creating it, and then querying it with the correct output. But I'm still getting the warnings. If anyone has seen this before I would appreciate the help.

Thanks




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users