SitePoint Sponsor

User Tag List

Results 1 to 13 of 13

Thread: substr()

  1. #1
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    substr()

    Hi,

    Im not understanding what substr() is doing in the code below?

    The query works if the "code" field is a number (int) but wont work if the field holds numbers and letters... I am trying to make the query work when the "code" field has numbers and letters..

    Code:
    	$query = 'SELECT * FROM products WHERE code IN (';
    	foreach ($_SESSION['cart'] as $key => $value) {
    	$query .= $key . ',';
    	}
    	$query = substr ($query, 0, -1) . ')';
    	$result = mysql_query($query);
    
    	
    	$total = 0;
    	while ($row = mysql_fetch_array ($result)) {
    Thanks

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,118
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    The substr is removing the last comma from the $query.

    The foreach is taking all of the keys in $_SESSION['cart'] (which seem to be numeric), and concatenating them to 'SELECT * FROM products WHERE code IN (' with a comma after each key.

    So if you had 3 items in your cart with keys [0], [1], [2], your query after the foreach would be
    'SELECT * FROM products WHERE code IN (0,1,2,'

    Then the substr, removes the last character in the query, so now the query becomes and concatenates the closing parenthesis
    'SELECT * FROM products WHERE code IN (0,1,2)'

    You are correct in that the query does NOT support the key having characters, it must be numeric.

    Hope that helps

  3. #3
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ahh yea that makes sence..

    $_SESSION['cart'] doesnt that hold the $code and the key? and not taking the value into account?

  4. #4

  5. #5
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok.. im abit lost... can the query be writen in another way so I can use the code field that holds numeric and characters? aposed to say id field that holds numeric?

  6. #6
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,118
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Does the code column in your database allow numbers and characters? If it doesn't I'd need to know what column you'd actually want to query against.

  7. #7
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea the code column holds numbers and characters.. and I want to use that column and drop the id column as it makes it hard to keep my items up dated with csv file.. as the code never changes but the id field often changes when products are added and removed in the csv file

  8. #8
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,118
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Got it, okay, here you go
    PHP Code:
        $query 'SELECT * FROM products WHERE code IN (';
        foreach (
    $_SESSION['cart'] as $key => $value) {
        
    $query .= "'" $key "',";
        }
        
    $query substr ($query0, -1) . ')';
        
    $result mysql_query($query);

        
        
    $total 0;
        while (
    $row mysql_fetch_array ($result)) { 
    Now it should produce a query similar to
    "SELECT * FROM products WHERE code IN ('0','1','2')"

    Thus now that the $key's are in quotes, it can accept characters as well as numbers.

  9. #9
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did try quotes as it seemed like the answer just like a standard query, no quotes needed around numbers.. But placed the quotes in all the wrong places..

    Thanks..

  10. #10
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,118
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by _matrix_ View Post
    I did try quotes as it seemed like the answer just like a standard query, no quotes needed around numbers.. But placed the quoted in all the wrong places..

    Thanks..
    Not sure I understand your statement, being that code is a varchar type field (both letters and numbers), you should have used quotes in your query all of the time.

  11. #11
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I used to use "id" which was int field, but now want to use code field instead which is varchar..

  12. #12
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    as a side note, that could be simplified a bit using implode and array_keys:
    PHP Code:
    $query 'SELECT * FROM products WHERE code IN ('.implode(',',array_keys($_SESSION['cart'])).')'
    and for non-numerics:
    PHP Code:
    $query 'SELECT * FROM products WHERE code IN ("'.implode('","',array_keys($_SESSION['cart'])).'")'
    NOTE: as always, values should be sanitized.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  13. #13
    SitePoint Guru
    Join Date
    Aug 2004
    Location
    Earth
    Posts
    739
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    as a side note, that could be simplified a bit using implode and array_keys:
    PHP Code:
    $query 'SELECT * FROM products WHERE code IN ('.implode(',',array_keys($_SESSION['cart'])).')'
    and for non-numerics:
    PHP Code:
    $query 'SELECT * FROM products WHERE code IN ("'.implode('","',array_keys($_SESSION['cart'])).'")'
    NOTE: as always, values should be sanitized.
    Thanks.. Looks like a good option.


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
  •