SitePoint Sponsor

User Tag List

Results 1 to 17 of 17
  1. #1
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry Can not seem to be able to group all entries of the same group on the same row...

    I am trying to group all the entries for one username to retrieve all of the columns in that same group. Example a user name will be entered along several other parts of data like name phone number email, things like that. But it puts all the data into the data base like a stair step leading down and increments every following column to the next row, since they are not all grouped on the same row I can't get but the first entry, the user name which is what I already know... How in the world is this done right, I can't figure this out!

    Here is what I tried and it does just what I just described, looking at the data in phpmysql it looks like this:

    ___column 1____column 2_____column 3______column 4____.............>

    something-user
    _____________guys-name
    _________________________555-555-5555
    ______________________________________email@email.com

    So it does not work right, here is the php script I tried that did this type of result:

    include_once '../common/config.php'; //$tokenkey arrary and other strings for user, password, database for mysql conect
    $token_check=NULL;
    $token=NULL;
    for ( $count = 0; $count <= 19; $count ++) {
    for ( $counter = 0; $counter < 20; $counter ++) {
    $rand = rand ( 1, 62);
    $token[$count]=$token[$count].$tokenkey[$rand];
    }
    }
    //$token array has 19 strings in it
    $array=array(username,password,firstname,lastname,address,city,state,country,zipcode,sitename,options,verified,approved,geosignup,geologon,optionsnew,geooptionsnew,summaryledger,couponcode,websitehtml);

    $con=mysql_connect($host,$user,$password);
    @mysql_select_db($database, $con) or die( "Unable to select database");
    @mysql_query("CREATE TABLE users (id MEDIUMINT NOT NULL AUTO_INCREMENT,UNIQUE KEY(id),
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    zipcode VARCHAR(100) NOT NULL,
    sitename VARCHAR(50) NOT NULL,
    options VARCHAR(300) NOT NULL,
    verified VARCHAR(10) NOT NULL,
    approved VARCHAR(10) NOT NULL,
    geosignup VARCHAR(2000) NOT NULL,
    geologon VARCHAR(2000) NOT NULL,
    optionsnew VARCHAR(10) NOT NULL,
    geooptionsnew VARCHAR(300) NOT NULL,
    summaryledger VARCHAR(300) NOT NULL,
    couponcode VARCHAR(20) NOT NULL,
    websitehtml VARCHAR(5000) NOT NULL
    ) ENGINE=MyISAM");

    //makes all the columns nicely...

    //entering all the 19 strings into the data base "users"...

    $cnt=0;
    foreach ($array as &$var) {
    if($cnt == 0 || $cnt == 1 || $cnt == 2 || $cnt == 3 || $cnt == 9 ||$cnt == 11 || $cnt == 12 || $cnt == 15 || $cnt == 18) {
    @mysql_query("INSERT INTO users (".$var.") VALUES ('$token[$cnt]')");
    }
    if($cnt >= 4 && $cnt <= 8) {
    @mysql_query("INSERT INTO users (".$var.") VALUES ('$token[$cnt]')");
    }
    if($cnt == 10 || $cnt == 16 || $cnt == 17) {
    @mysql_query("INSERT INTO users (".$var.") VALUES ('$token[$cnt]')");
    }
    If($cnt == 13 || $cnt == 14) {
    @mysql_query("INSERT INTO users (".$var.") VALUES ('$token[$cnt]')");
    }
    if($cnt == 19) {
    @mysql_query("INSERT INTO users (".$var.") VALUES ('$token[$cnt]')");
    }
    $cnt++;
    }
    unset($var);

    //now it is that big mess I talked about!!

    Something I am doing is very wrong, please tell me how this should be done. Tring to figure it out from the manual is getting me no place but more confussed..

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbe_donald View Post
    How in the world is this done right, I can't figure this out!
    the answer is, put all the data values onto the same row

    instead of one INSERT statement per value, with a different "token" each time, collect your data values and submit them all at once with a single INSERT statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think my problem is in the understanding of how this database prosses the data, I can see that a simple string can be loaded and placed in spicific areas of the database, very similar to an array (stacked) and that it can be prossesed and shifted to be orginized to be easier and quicker to manipulate with php, I will figure out the syntax in time but I am missing the basic understanding of the pinciple funtions and commands and the method in wich the data is brought into mysql and even more interesting how it comes back out, and in what form. It seems that it does not come back out in just a simple string to a veriable as it goes in. I see there is a prosses to retrieve it and to bring it back into a usable form again, that information is what I am not understanding, the manual is not that detailed. Could you if you would list out some basic commands and bool, if I have a better understanding of how mysql works I will have it in no time. It is differant than the prosses I already know (perl, php, xhml 2.0, css 2.1, vbasic, some primary langauges (machine [assembly]) [mac, pc] and just a tiny bit of java script. All are pretty easy for me, only took a few hours to begin to use seriously, but for some reason mysql just completely has me baffeled, it seems very simple but I can not get almost anything to work at all even by going from the manual and experimenting. I spend hours of it doing nothing, no data in or out. When I do get some in it is all messed up and won't come back out, very frustrating... I am missing something vital for sure, please point the way to my understanding mysql that has my utmost interest at this time.... information is what I need, details where can I find them? Thankx Please show me what you have suggested in script form and what it is doing, I what to learn this stuff.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbe_donald View Post
    I am missing something vital for sure
    i believe you are missing the concept of a table row

    a table row consists of a collection of column values, and you would insert a row of data into a table as follows:
    Code:
    INSERT 
      INTO users 
         ( username 
         , password 
         , firstname
         , lastname 
         , address 
         , city 
         , state 
         , etc )
    VALUES
         ( 'todd42'
         , 'opensesame'
         , 'Todd'
         , 'O''Toole'   
         , '123 Main Street'
         , 'Eureka'
         , 'KS'
         , 'asdfasdf' )
    see? the entire row goes in at once, not token by token as you were trying to do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you that is exactly what I needed to know, the order is very important I understand now. I have another issue with setting the index properly. It seems not to like the combinations I have been trying. For instance '(id INT [MEDIUMINT] (9) UNIQUE [PRIMARY] KEY(id))' what is the proper syntax for this coomand? the "(9)" is for 9 digit intiger? I understand VARCHAR(?) the "?" is the max number of chactors but in TEXT(?) I have not discovered what the "(?)" desinates apparently it is columns instead? Thankx for your help in learning mysql!

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbe_donald View Post
    For instance '(id INT [MEDIUMINT] (9) UNIQUE [PRIMARY] KEY(id))' what is the proper syntax for this coomand?
    CREATE TABLE foo ( id MEDIUMINT NOT NULL PRIMARY KEY, ...

    Quote Originally Posted by newbe_donald View Post
    the "(9)" is for 9 digit intiger?
    no, the number here means only how many digits to display when using ZEROFILL

    INTEGER(1) and INTEGER(9) and INTEGER(937) all hold exactly the same range of integers

    TEXT does not take a length
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes that is what I thought TEXT does not take a legnth, so what is that spacificly to represent [TEXT(?)]? Grouping is just done by the command [GROUP BY]. This is starting to makes sense to me now. Another question, in what form does the database output the data in, ie (alpha, numeric, symbols) and how can it be put into a string or array in php? Thankx

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by newbe_donald View Post
    This is starting to makes sense to me now.
    i am quite relieved to hear that
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am understanding this more, makes sence to me. But I tried it and appairently my syntax is incorrect because this created the table users perfectly but did not enter any VALUES although the array $token has 19 values. I think this answer should settle my create / insert issues for good:

    $con=mysql_connect($host,$user,$password);
    @mysql_select_db($database, $con) or die( "Unable to select database");
    @mysql_query("CREATE TABLE users (id INT(11) NOT NULL AUTO_INCREMENT,UNIQUE KEY(id),
    username VARCHAR(50) NOT NULL,
    password VARCHAR(50) NOT NULL,
    firstname VARCHAR(50) NOT NULL,
    lastname VARCHAR(50) NOT NULL,
    address VARCHAR(100) NOT NULL,
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100) NOT NULL,
    country VARCHAR(100) NOT NULL,
    zipcode VARCHAR(100) NOT NULL,
    sitename VARCHAR(50) NOT NULL,
    options VARCHAR(300) NOT NULL,
    verified VARCHAR(10) NOT NULL,
    approved VARCHAR(10) NOT NULL,
    geosignup VARCHAR(2000) NOT NULL,
    geologon VARCHAR(2000) NOT NULL,
    optionsnew VARCHAR(10) NOT NULL,
    geooptionsnew VARCHAR(300) NOT NULL,
    summaryledger VARCHAR(300) NOT NULL,
    couponcode VARCHAR(20) NOT NULL,
    websitehtml VARCHAR(5000) NOT NULL
    ) ENGINE=MyISAM");

    @mysql_query("INSERT INTO users (
    username,
    password ,
    firstname,
    lastname,
    address,
    city,
    state,
    country,
    zipcode,
    sitename,
    options,
    verified,
    approved,
    geosignup,
    geologon,
    optionsnew,
    geooptionsnew,
    summaryledger,
    couponcode,
    websitehtml
    ) VALUES (
    '$token[1]',
    '$token[2]',
    '$token[3]',
    '$token[4]',
    '$token[5]',
    '$token[6]',
    '$token[8]',
    '$token[9]',
    '$token[10]',
    '$token[11]',
    '$token[12]',
    '$token[13]',
    '$token[15]',
    '$token[16]',
    '$token[17]',
    '$token[18]',
    '$token[19]'
    )");

    mysql_close($con);

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    although you did not know it, your INSERT did not even run, it failed on a syntax error

    it is easy to spot the error with an eyeball check

    you are missing two tokens
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Warning: mysql_fetch_array(): 4 is not a valid MySQL result

    LOL!!! It would help if I could count all the way to 10 not only once but twice!! Must be a Freudian slip of a sorts?

    But seriously now, one more question and I'm on my way to get some stuff done, I am not sure in what form the row (string?) $result comes out from mySQL.. Here is the script to pull the row grouped by column usermane with value $token[1]:

    It gives these two errors:

    Warning: mysql_fetch_array(): 4 is not a valid MySQL result resource in /XXX/XXX/test.php on line 109

    Warning: mysql_free_result(): 4 is not a valid MySQL result resource in /XXX/XXX/test.php on line 113


    $result=mysql_query("SELECT username
    , password
    , firstname
    , lastname
    , address
    , city
    , state
    , country
    , zipcode
    , sitename
    , options
    , verified
    , approved
    , geosignup
    , geologon
    , optionsnew
    , geooptionsnew
    , summaryledger
    , couponcode
    , websitehtml
    FROM users WHERE username = '$token[1]'");
    if ($result !== "") {
    for ( $count = 0; $count <= 19; $count ++) {
    $out++;
    $token_check=NULL;
    while($row = mysql_fetch_array($result, MYSQL_BOTH)) { // ERROR line 109
    $cnt++;
    $token_check=$token_check.$row[$cnt];
    }
    mysql_free_result($result); // ERROR line 113
    $output_array[$out]=$token_check;
    }
    }else{
    echo "Database users creation FAILED. Token created for (username) is: ".$token[1]."<br />";
    }

    The two lines that ERROR (109 & 113) are listed to the right of //

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    can't help you man, sorry, i don't do php, this is the mysql forum, eh
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK.. I'll take the scipt over to a PHP forum, but maybe you could give me a better insight into what the raw form the data comes out of mySQL and how to get into string form to be used? $result comes out to a value of a funtion identified by a number as in this case it is "Resource id #4" but is unusable in that form. What is the prosses to put into a string value?

    I have moved this question to PHP:

    http://www.sitepoint.com/forums/show...76#post4970776

    Thankx

  14. #14
    SitePoint Member
    Join Date
    Sep 2011
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face PHP mySQL error with retrieving string data table row resource id #4 & while()

    I am not sure in what form the row data $result comes out from mySQL.. Here is the script to pull the row grouped by column usermane with value $token[1]:

    It gives these two errors:

    Warning: mysql_fetch_array(): 4 is not a valid MySQL result resource in /XXX/XXX/test.php on line 109

    Warning: mysql_free_result(): 4 is not a valid MySQL result resource in /XXX/XXX/test.php on line 113

    PHP Code:
    $result=mysql_query("SELECT username
    , password
    , firstname
    , lastname
    , address
    , city
    , state
    , country
    , zipcode
    , sitename
    , options
    , verified
    , approved
    , geosignup
    , geologon
    , optionsnew
    , geooptionsnew
    , summaryledger
    , couponcode
    , websitehtml
    FROM users WHERE username = '
    $token[1]'");
    if (
    $result !== "") {
    for ( 
    $count 0$count <= 19$count ++) {
    $out++;
    $token_check=NULL;
    while(
    $row mysql_fetch_array($resultMYSQL_BOTH)) { // ERROR line 109
    $cnt++;
    $token_check=$token_check.$row[$cnt];
    }
    mysql_free_result($result); // ERROR line 113
    $output_array[$out]=$token_check;
    }
    }else{
    echo 
    "Database users creation FAILED. Token created for (username) is: ".$token[1]."<br />";

    The two lines that ERROR (109 & 113) are listed to the right of //

    How is this data ($result) properly possesed into a string value? The value I see for $result at this point is only a funtion (Resource id #4) when echo $result; it has no string value as yet by this script. I'm very new with mySQL trying to get the basics straight so I can figure it out. Thankx
    Last edited by guido2004; Oct 11, 2011 at 01:52. Reason: added php tags

  15. #15
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Your getting the errors because the query has failed, most likely because the $token[1] in the WHERE clause is not enclosed in { and }
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  16. #16
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by newbe_donald View Post
    OK.. I'll take the scipt over to a PHP forum, but maybe you could give me a better insight into what the raw form the data comes out of mySQL and how to get into string form to be used? $result comes out to a value of a funtion identified by a number as in this case it is "Resource id #4" but is unusable in that form. What is the prosses to put into a string value?

    I have moved this question to PHP:

    http://www.sitepoint.com/forums/showthread.php?788984-PHP-mySQL-error-with-retrieving-string-data-table-row-resource-id-4-amp-while()&p=4970776#post4970776

    Thankx
    Two threads merged. If any problem turns about to be in another area for example this thread started off as a MySQL problem but has turned out to be a PHP problem when just report the thread and request a thread move to the more appropriate forum. Thanks
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  17. #17
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,508
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    To get useful information in case of a query error, display mysql_error() and the query you're executing, for example using the 'or die()' construction:
    PHP Code:
    $query "
      SELECT 
          username
        , password
        , firstname
        , lastname
        , address
        , city
        , state
        , country
        , zipcode
        , sitename
        , options
        , verified
        , approved
        , geosignup
        , geologon
        , optionsnew
        , geooptionsnew
        , summaryledger
        , couponcode
        , websitehtml
      FROM users 
      WHERE username = '" 
    $token[1] ."'
    "
    ;
    $result=mysql_query($query) or die('mysql error ' mysql_error() . ' in query ' $query); 


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
  •