SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Syntax of SQL query in PHP code

    Sorry, this is probably something that falls between PHP and mySQL, so apologies if this is in the wrong section.

    I am using a bit of code that autocompletes a field when someone types:

    $rs = mysql_query('select Lodge from lodges Lodge like "%'. mysql_real_escape_string($_REQUEST['term']) .'%" order by Lodge asc limit 0,50', $dblink);

    Which works great, and looks up all the records in the table lodges.

    But I'd like it to filter on a particular field, and can't get it to work - basically just need to add something like the following to filter out records where a particular field is not blank:

    select Lodge from lodges WHERE field name <> '' AND lodges like....

    This must be an easy one, but I can't seem to get the syntax.

    Thanks.

  2. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Please be aware that the mysql_* extension is now deprecated as of the current version of PHP and will very likely be removed from the next 5.x version and will likely not be in PHP 6.x (when it eventually is released). You should migrate over to either the mysqli_* extension or to PDO. PDO is a better choice as it doesn't tie you down so much to a particular database server software.

    Once you have migrated you should use Prepared Statements to prevent SQL Injection attacks. Have a read of this article from the PHP manual, it shows how to use prepared statements with PDO and also explains the principle.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    Is the field NULL or contains an empty string?

    If it's null, you should use:

    Code:
    WHERE name IS NOT NULL
    Or check if it's not null AND check if it's not empty:

    Code:
    WHERE (name IS NOT NULL AND name <> '')
    Like SpacePhoenix said, you should migrate to mysqli_ or PDO.
    Except if you're just trying out stuff and not planning on putting your website/web app available to the Internet.

    If you're going to put it live on the net:
    If you're trying out stuff and not planning on changing your DB server (mySQL for something else), then change it with mysqli_, it will be easier.

    I wrote an article on my blog about the difference between mysql_ and mysqli_ if you're interested. The main reason to migrate is because of SQL injections (a security hole).

  4. #4
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,014
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by xMog View Post
    Like SpacePhoenix said, you should migrate to mysqli_ or PDO.
    Except if you're just trying out stuff and not planning on putting your website/web app available to the Internet.

    If you're going to put it live on the net:
    If you're trying out stuff and not planning on changing your DB server (mySQL for something else), then change it with mysqli_, it will be easier.

    I wrote an article on my blog about the difference between mysql_ and mysqli_ if you're interested. The main reason to migrate is because of SQL injections (a security hole).
    PDO is more preferable to mysqli_* as it makes the code more portable, if you were to ever change database server software (you can't ever be 100% certain that you'll always stick with MySQL), then all that would need changing would be the details for connecting to the database and any MySQL specific SQL
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys.

  6. #6
    SitePoint Zealot bronze trophy xMog's Avatar
    Join Date
    Mar 2011
    Posts
    145
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)
    SpacePhoenix: If he's a beginner trying stuff out, why would he care? And with my 10+ years of experience on more than 100 projects, we never, ever, had to change the database. So lets agree do disagree then

    P.S. A place that using an ORM make sense is with "products" that you sell/share, IMO. It just depends on the context.

  7. #7
    SitePoint Member
    Join Date
    Nov 2006
    Posts
    18
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Agree with xMog , in 15 years as a developer I've never had to change a database.

  8. #8
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    689
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)
    I have had the pleasure of adding in new databases to support several times though never in php. Not fun. Having a generic connection object such as pdo is only a tiny part of the battle. All databases have enough quirks that it becomes a pain to back fit one.

    Having said that, the reason for using pdo by default is that it supports named parameters. Makes the code easier to develop and support. This one capability trumps any perceived advantage of using native interfaces.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •