Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11
  1. #1
    Member unclekyky's Avatar
    Join Date
    Sep 2003
    Posts
    62
    Member #
    2938
    Ok lets say i have a table that looks like this http://www.webjunky.us/images/mysql.gif . What i want to do is select all the fields where the username is unclekyky and the value is greater than 0 (only whole numbers will be used). Then i want to be able to display the column header (i.e. .45 Colt) along with the value using some kind of loop.
    I can do all of this only my way requires me to say an if statement for each item (column).

    Please ask for clarification if it is confusing.
    Thanks for any help

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    the SQL will look like this:

    Code:
    SELECT * FROM table_name 
    WHERE username = 'unclekyky' 
       AND ("Bag of Biscuits" > 0 OR ".45 Colt" > 0);
    Which brings up a point: DON'T PUT SPACES OR SPECIAL CHARACTERS IN YOUR COLUMN NAMES !!!

    Database objects should follow the naming convention of lowercase with underscores (or alphanumeric with underscores, if you want to be lax).

  4. #3
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    Quote Originally Posted by unclekyky
    Then i want to be able to display the column header (i.e. .45 Colt) along with the value using some kind of loop.
    I can do all of this only my way requires me to say an if statement for each item (column).

    Please ask for clarification if it is confusing.
    Thanks for any help
    I think it's a bit unclear.
    Transio's suggestion will extract all rows containing values above '0' in the specified columns.

    So my question is; was that your question?

    If you want to display the result in MySQL monitor or another MySQL interface, then you get a table with column heads corresponding to the ones you have in the table, followed by rows of extracted values.

    If you want a different columnname than the one in your original table, you can specify the column in question in your query, and use 'AS' (new name).
    If you have a column 'userName' and you want it to display as 'member':
    SELECT userName AS 'member' FROM...

    You can also create new columns this way (for display). If you for example have columns containing price and quantity, you could extract both of these and multiply them during your query to produce a third column in the resultset.
    SELECT price, quantity, (price * quantity) AS 'total' FROM...

    ########

    If, on the other hand, you want to display the resultset in some webpage through PHP, then you have to iterate through the resultset in some kind of loop, as PHP only extracts one row at the time. If that's what you plan to do, you can just write in plaintext what you want displayed in front of the extracted value, or you could use mysql_field_name($res, $iterator) which will display the original columnname.
    PHP Code:
    $res=mysql_query($sql) or die("Error: ".mysql_error());

    while(
    $row=mysql_fetch_assoc($res))
    {
     echo 
    "Username: ".$row['userName'].", Biscuits: ".$row['Bag_Of_Biscuits'].", No of Colt 45's: ".$row['.45_Colt']."</BR>";


    If neither of the above posts are what you're looking for, you have to clarify.
    S. Rosland

  5. #4
    Member unclekyky's Avatar
    Join Date
    Sep 2003
    Posts
    62
    Member #
    2938
    Thank you very much guys that helps a lot.
    One more question though, if I have a table with 100 columns do i need the write each name out like transio said?

  6. #5
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    No, as long as you want to extract all columns you can use the wildcard '*' SELECT * FROM....

    If you want to manipulate columns though, you would have to specify all column names. Let's say you had a column containing date information and you for varying reasons wanted to add or subtract a certain amount of time from the table record. You could not double select that column through a wildcard + a column modyfier:
    SELECT *, DATE_SUB(entered, INTERVAL 4 MONTH) FROM...
    that would lead to an error.

    You can use the wildcard as long as you just want WHERE clauses to limit or order your resultset. It's just when you want to manipulate column content through query, or you for some other reason want to limit the amount of column data to be retrieved, you need to specify columns in your query.

    So in short, anytime you want to manipulate any of the column information (through the query), you have to specify all column data you want to be retrieved.
    S. Rosland

  7. #6
    Member unclekyky's Avatar
    Join Date
    Sep 2003
    Posts
    62
    Member #
    2938
    What I was trying to say was this:
    Code:
    SELECT * FROM inventory WHERE username='unclekyky'
    This query selects all the columns but what i want to do is select only the columns that are greater than 0. I could do it the way transio explained but if I have one hundred columns I would have to type each one out. Is there a way to use wildcards in the WHERE argument.
    Code:
    SELECT * FROM inventory WHERE username='unclekyky' AND * > 0
    I know that wont work but i want something like it.

    Im sorry if you answered this already, but Im a little slow

    Also is there a way to use the while loop, mysql_fetch_assoc, and mysql_field_name to make life easier and the script shorter?

  8. #7
    Senior Member seanmiller's Avatar
    Join Date
    Sep 2003
    Location
    Glastonbury, UK
    Posts
    868
    Member #
    3263
    Liked
    1 times
    Bit confused about this... are you sure that you've designed your database correctly? You say that you only wish to select the columns that are greater than zero ?

    If you had three columns... username, thingname, items... then you'd say something like...

    SELECT thingname, items
    FROM mytable
    WHERE username = 'kasjdksadja'
    AND items > 0

    Relationally this is actually still not correct as it is entirely denormalised... "things" and "users" should be tables in their own right and the allocation of things to users should be via another table... but without knowing more about the application it would be difficult to come up with an exact database design... for example, how do things get allocated to users?

    Sean

  9. #8
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    You would have to type out each columnname.
    I agree with Sean that your example table is a bit odd, I assume you just wipped something up to illustrate your question.

    I can't really imagine a table containing 100 columns, that should definitly be split into multiple tables relating to each other. I'm not sure you got the concept right when you talk about only extracting columns containing values. Each column can contain thousands of rows. If one of those rows contained a zero in a particular column while the rest of the rows held values, would you want to drop the whole column?
    S. Rosland

  10. #9
    Member unclekyky's Avatar
    Join Date
    Sep 2003
    Posts
    62
    Member #
    2938
    OK I'll say a little more.

    I have 3 tables: users, items, and inventory. Each contains info relative to its name. So in inventory there could be 50 users (rows) and 75 items (columns). What I want to do is select the items only for 1 specific user (i.e. unclekyky). The number in the column represents the amount of items that user has. Thats why I want to select only the columns that are greater than zero. (I guess I could select all of them and just not display the ones with zero.)
    I was trying to figure out what the best way to to this was, thats why I posted. I think you guys answered my question though (that I have to specify all columns in the WHERE argumnet).

    Thanks for you help and if you have anything more to say that would make this easier then I am all ears.

  11. #10
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    You should use a 'select all', and use script to skip empty columns.
    You could extract the column headers and place them in an array. That way you could use the array to print out a lead text in front of your items.
    PHP Code:
    $res=mysql_query($sql) or die("Failed: ".mysql_error());

    for(
    $i=0$i<mysql_num_fields($res); $i++)
    {
     
    //extracts column names to array $field
     
    $field[]=mysql_field_name($res$i);
    }

    //The while loop is only neccessary if more than one row is extracted.
    //(but works for single rows as well of course)
    while($row=mysql_fetch_array($res))
    {
      for(
    $j=0$j<mysql_num_fields($res); $j++)
      {
       if(!
    $row[$j]){continue;} //Skips empty columns
       
    echo $field[$j].": ".$row[$j].", ";
      }
     echo 
    "<BR />"//makes a new line for the next user if more than one's selected

    The above is not tested (as I'm far away from any php server at the moment), but in principle it should do what you're asking for.
    S. Rosland


Page 1 of 2 1 2 LastLast

Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:41 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com