www.webdeveloper.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Thread: Help with split please

  1. #1
    Join Date
    Sep 2006
    Posts
    15

    Help with split please

    I am trying to split the results of an sql query but I am having some issues.

    I am trying to use the results of the query as a conditional argument in an if statement and it works for the most part however, some of the results from the query have comma delimited values and some do not. When I apply the if statement later in the program it works when the results of this code show a single value but it does not work when the results of this query show a comma seperated result so I am trying to split the results of this query on any commas and return the results comma free and as seperate array elements.

    ______________________________________________________________

    $sql_statement ="SELECT product_xcat FROM product";

    @cm_xcat = database_call('product','SELECT',$sql_statement);

    foreach $row(@cm_xcat) {

    ($cm_xcat) = @$row;

    @cm_xcat_results = split(/,/, $cm_xcat);

    } ######## End of foreach statement.

    ____________________________________________________________

    This currently only removes the commas but does not return the comma delimited values as seperate array elements.

    Thanks to everyone for your assistance,

    -vbsaltydog

  2. #2
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    I'm not sure exactly what you want, but this builds a AoA for your data.

    _and_
    use strict;
    also please put your code in 'code' tags

    Code:
    #!/usr/bin/perl -w
    use strict;
    use Data::Dumper;
    
    #$sql_statement ="SELECT product_xcat FROM product";
    #@cm_xcat = database_call('product','SELECT',$sql_statement);
    my @cm_xcat = <DATA>; ## simulates your database call
    
    for (my $i=0; $i<(@cm_xcat); $i++) {
    	my @splitdata = split(/,/, $cm_xcat[$i]);
    	$cm_xcat[$i] = \@splitdata;
    }
    
    print Dumper(\@cm_xcat);
    
    __DATA__
    product 1
    product 2, white
    product 3, pink, and blue

  3. #3
    Join Date
    Sep 2006
    Posts
    15
    Thank you for the info. It didnt solve my issue but it is appreciated.

    I have found what I need to do but I dont know how to do it. I am pulling db info based on conditions in the WHERE clause however, the field that the WHERE clause is looking is the problem field. If the field holds a single category name then my code works great but if the field holds more than one category name seperated by commas then it breaks my code. I cant edit the field values directly in the db as the rest of the cart uses this field and its values seperated by commas if more than one value is present.

    So the question is....how do I alter the contents of the field product_xcat before the WHERE clause reads it in the code below?


    Code:
    $sql_statement =" 
    
    SELECT product.product_id, product.product_name, product.product_imgsm,
    product.product_xcat, category.category_header_name,category.category_imgsm 
    FROM product JOIN category ON product.product_xcat=category.category_id
    WHERE product_xcat like $dbins_fd_ref ORDER BY RAND($product_name) limit 3 
    
    ";
    
    my @header = database_call('product','SELECT',$sql_statement);

  4. #4
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    Quote Originally Posted by vbsaltydog
    If the field holds a single category name then my code works great but if the field holds more than one category name seperated by commas then it breaks my code.
    Do you mean that 'product_xcat' can contain a comma seperated list of items?
    If so, you might be able to use a REGEXP within your SQL statement.

    WHERE product_xcat REGEXP '$dbins_fd_ref' ORDER BY.....
    $dbins_fd_ref in quotes, I think.

    Just a guess but it might work.
    Last edited by Nedals; 09-27-2006 at 09:38 PM.

  5. #5
    Join Date
    Sep 2006
    Posts
    15
    A regex wont work for this. The statement compares the value in the product_xcat with $dbins_fd_ref to pull the correct info where $dbins_fd_ref equates to the category name of the page being viewed in the browser and product_xcat equates to a database product's parent category. It works fine if there is only 1 category listed as a parent of a product but when a product belongs to more than one category, those categories are comma delimited in the product_xcat field and this causes an issue.

  6. #6
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    Is this your setup, or am I totally mis-understanding the problem?
    Code:
    database: (in code tage to maintain spacing) 
    product_id | product_name | .. | product_xcat
    1          | shoe         | .. | a,b,c
    SQL to return product data if $dbins_fd_ref is 'a', 'b', or 'c'

    The REGEX method I gave you should work. (qualifier: I think! )

  7. #7
    Join Date
    Sep 2006
    Posts
    15
    You are right on the money with understanding the table structure and the condition set and although your code does work somewhat (same as all of my other methods), it works if there is only one value in the product_xcat field. If there are two values seperated by comma or seperated by spaces it returns no results.

    product_xcat
    ___________

    value #This works
    value1,value2 #Returns nothing
    value1 value2 #Returns nothing


    Thanks for your help.

  8. #8
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    So you got my curiosity up and this works for me...
    Code:
    my $cond = 'AA';
    # where list contains 'AA,BB,CC,DD'
    my $sth = $dbh->prepare("SELECT data FROM table WHERE list REGEXP '$cond'"); # MUST be quoted
    $sth->execute();
    $sth->bind_columns(\my ($data));
    while ($sth->fetch()) { print "$data<br>"; }
    
    -OR better using placholders
    my $sth = $dbh->prepare("SELECT data FROM table WHERE list REGEXP ?");
    $sth->execute($cond); # no quotes needed

  9. #9
    Join Date
    Sep 2006
    Posts
    15
    I appreciate your reply. I tried your new code and it does the same thing as every other solution I have tried.
    It works when looking at a single string but doesnt work on comma seperated strings. I think it worked for you because
    of the ease of pattern matching AA,BB,CC,DD but I am not the expert.

    I have shifted gears to a new technique and am hopeful on this as the other methods have burned me out.
    Since I am trying to show results based on a comma separated value that is stored in a scalar I was thinking
    of splitting the scalar into an array (split on the commas) before the SQL Select and then try to match based on the array elements.

    Ex.

    Code:
    $scaler = "match1,match2,match3,match4";
    @array = split(',', $scaler);
    
    $sql = "SELECT data FROM table WHERE condition LIKE $array[0,1,2,3,4,5]";
    This seems to be working but it will only match on one array element currently.
    Can anyone help me figure out how to have this WHERE clause match based on ANY of the listed array elements?

    Any ideas are appreciated.

  10. #10
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    Quote Originally Posted by vbsaltydog
    You are right on the money with understanding the table structure and the condition set
    Based on your last post, I think NOT.

    I think you are now saying that $dbins_fd_ref can be a comma-seperated list, not 'product_xcat'.
    Code:
    database: (in code tage to maintain spacing) 
    product_id | product_name | .. | product_xcat
    1          | shoeA        | .. | a
    2          | shoeB        | .. | b
    3          | shoeC        | .. | c
    
    $dbins_fd_ref = 'a,b,c';
    If that's the case, you are dealing with a different problem.

    What you need to do is create a multi-condition where clause
    ... WHERE product_xcat IN ($dbins_fd_ref)
    $dbins_fd_ref is a comma seperated list;

    Now it will return a result for product_xcat = a, b, or c

    UPDATE:
    That will probably not work as shown. You will likely have to set this up using placeholders because you are using strings. If the above is a correct interpretation, I'll think about how your SQL statement needs to look.
    Last edited by Nedals; 09-28-2006 at 11:46 PM.

  11. #11
    Join Date
    Sep 2006
    Posts
    15
    Thanks for the responses. Yes, after doing the suggested coding from your replies I realized I was trying to act on the wrong variable. The whole time I was trying to split or choose comma separated values from the $dbins_fd_ref when I needed to be choosing from/splitting the product_xcat value but here's my issue...

    Code:
    $sql_statement =" 
    
    SELECT product.product_id, product.product_name, product.product_imgsm,
    product.product_xcat, category.category_header_name,category.category_imgsm 
    FROM product JOIN category ON product.product_xcat=category.category_id
    WHERE product_xcat like $dbins_fd_ref ORDER BY RAND($product_name) limit 3 
    
    ";
    
    my @header = database_call('product','SELECT',$sql_statement);
    is my code but how can I get the results of the db field product_xcat prior to running this select statement?

    I know how to use a select statement to get the value of the db field product_xcat but it would need to be in a different code block and so the results would not be available to the select statement in the code shown above due to the namespace restrictions.

    Someone please tell a noob how to run a select statement and store the results into a variable that is available to the following block of code i.e. next subroutine.

    Thanks to all who are willing to assist.

  12. #12
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    vbsaltydog,
    I'm still a little confused about this comma-seperated item.

    and this I really don't get
    I know how to use a select statement to get the value of the db field product_xcat but it would need to be in a different code block and so the results would not be available to the select statement in the code shown above due to the namespace restrictions.

    In an earlier post of mine I asked if the comma-seperated list was saved in the database column 'product_xcat'.
    Your response: [i]You are right on the money.... [i/]
    See post #6 - In which case REGEXP should work

    In a later post you talk about: $scaler = "match1,match2,match3,match4"; which would indicate that the comma-seperated list is in the $dbins_fd_ref
    See post #10 - In which case IN should work.

    'LIKE' is not going to do what you want.

    Let's KISS.
    So, based on your last post....
    product_xcat a comma-seperated list in the database and
    $dbins_fd_ref a single value

    $sql =" SELECT <data> FROM product WHERE product_xcat REGEXP $dbins_fd_ref

    Will return <data> when '$dbins_fd_ref' appears in the comma-seperated list, 'product_xcat'

    Please clarify, and between us we can make this work.
    Maybe you can supply some actual values.

  13. #13
    Join Date
    Sep 2006
    Posts
    15
    Thanks for your reply. I was actually playing with REGEXP when you replied.
    Forget about the namespace isses. I got that figured out.

    Picking up where we left off as

    $dbins_fd_ref is single value
    product_xcat can be single value, or comma seperated values.

    I have created a new sql select that pulls the product_xcat if product_xcat REGEXP $dbins_fd_ref and it works. If the product_xcat is value_one_matching it works and if the product_xcat is value_one_nomatch,value2_matching it works and prints out the matching values as

    value_one_matching

    valueone_nomatch,value_two_matching

    so this part appears to be working with REGEXP. Thank you very much.

    Now my issue appears to be in my if /else for showing matching products..

    Code:
    $sql_statement =" 
    
    SELECT product.product_id, product.product_name, product.product_imgsm,
    product.product_xcat, category.category_header_name,
    category.category_imgsm FROM product JOIN category ON
    product.product_xcat=category.category_id 
    WHERE product_xcat REGEXP $dbins_fd_ref ORDER BY RAND($product_name) limit 3
    
    ";
    
    my @header = database_call('product','SELECT',$sql_statement);
    
    if ($header[0] > 0) {
    $dbins_fd_ref =~ s/\_/ /gs;
    
    print<<ENDOFTEXT;
    <tr>
    <td align="center" colspan="4"><b>Our most popular $page_title this month!</b></td>
    </tr>
    ENDOFTEXT
    
    foreach $row(@header) {
    
    ($product_id,$product_name,$product_imgsm,$product_xcat,$category_header_name,$category_imgsm)
    = @$row;
    
    $product_xcat =~ s/\_/ /gs;
    
    $product_id_encoded = vars_urlencode($product_id);
    
    &display_print('ste_cat_cmheader');
    
    } ######### End of foreach statement.
    
    } else {
    
    print "<td align=center>Have a question about $page_title?<br> 
    We have sales representatives standing by.<br> 
    Simply click on the Live Help Icon to speak with a salesperson immediately</td>";
    
    } ########## End of if statement;
    It only prints out the matching products if the matching value is singular. Any comma separated values matching presents the else statement results.

    Thank for helping with this.

  14. #14
    Join Date
    Sep 2006
    Posts
    15
    no. never mind. its not the if/else.

    Its something having to do with the sql select.
    As I said before, I created another sql select statement for testing.
    When using my testing code of :

    Code:
    $dbins_fd_ref = database_quote('category',$fd_ref);
    
    $cbcsql = "SELECT product_xcat FROM product WHERE product_xcat REGEXP $dbins_fd_ref";
    
    my @cbcarray = database_call('product','SELECT',$cbcsql);
    
    foreach $row(@cbcarray) {
    
    ($cbc_xcat) = @$row;
    
    #print "$cbc_xcat<br>";
    
    } ######### End of foreach statement.
    and print the output to the html page it returns the correct results.

    However, the results are not the same when using my non-testing sql of:

    Code:
    $sql_statement =" 
    
    SELECT product.product_id, product.product_name, product.product_imgsm, product.product_xcat,
    category.category_header_name, category.category_imgsm FROM product JOIN category ON 
    product.product_xcat=category.category_id WHERE product_xcat REGEXP $dbins_fd_ref 
    ORDER BY RAND($product_name) limit 3
    
    ";
    
    my @header = database_call('product','SELECT',$sql_statement);
    In this scenario it does not return proper results which is why the if/else is failing.

  15. #15
    Join Date
    Dec 2002
    Location
    Pleasanton, CA
    Posts
    2,132
    Quote Originally Posted by vbsaltydog
    ...It only prints out the matching products if the matching value is singular. Any comma separated values matching presents the else statement results.
    I'll make a guess...
    my @header = database_call('product','SELECT',$sql_statement);
    This only returns ONE row to the @header array. BUT.....
    Quote Originally Posted by vbsaltydog
    It only prints out the matching products if the matching value is singular. Any comma separated values matching presents the else statement results.
    Based on your code, the else result will only occur if $results[0] <= 0. So what should happen is that single result will be displayed and only one item of multi results will be displayed. But, it looks like @header is actually an AoA, so $results[0] is, in fact, an array reference. (not what your code indicates)

    You might try...
    if (scalar @header > 0) { ## any elements in the array

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles