Welcome to MacTalk Australia

the largest Australian community for Apple discussions and topics

Join the discussions, Register Now!
Page 1 of 2 1 2 LastLast
Results 1 to 20 of 28
  1. #1

    Default

    I have a table called "form-1a". It has a column called "user" and new columns will be added.

    whats the php script that:
    1. gets the current date (i already have this)
    2. checks in table (form-1a) if a column with that date exist
    3. if not add one
    4. then looks in the column "user" for the username ($user)
    5. then adds one to the column (named after the current date)

    so it would be like:

    user | 05/10/05 | 06/10/05
    ---------------------------------------------------------------------------
    designers_hub | 1 | 5

    THANKS!

    btw. i already have all the php connecting scripts to mysql. i just need to know the commands to change the database. also i only need a column to be added when this script is runned, so i don't need new columns to be added every day.

  2. #2

    Join Date
    Nov 2004
    Location
    Chasing rainbows
    Posts
    2,238

    Default

    Why would you keep adding a new column for every date? Surely defining a table with date, user and count fields then making the date a unique key will allow you to capture all the info without needing to modify the database structure.

    The you check the rowcount for a record with the current date. If it is zero then insert a new record with the urrent date. If it is greater than zero then increment the count for that record.
    Pass the crystal, spread the Tarot, in illusion comfort lies

    http://www.mistfall.com/

  3. #3

    Default

    its for when i export into into a excel format.

    actually what i just said reverse the date to rows and user to columns. the other way will make it a pain to make excel graphs.

    could you illusrate your method? i can't really visualise it? thanks

  4. #4

    Join Date
    Nov 2004
    Location
    Chasing rainbows
    Posts
    2,238

    Default

    One question: are you keeping an individual count for each user for each day?
    Pass the crystal, spread the Tarot, in illusion comfort lies

    http://www.mistfall.com/

  5. #5

    Join Date
    Jul 2004
    Location
    Gold Coast
    Posts
    166

    Default

    What you've suggested is a bad idea because adding data shouldn't change the structure of a database (the tables,columns and relationships) only the data contained in it (the rows). Graham's suggested that you structure your table like this:
    Code:
    user          | date     | logins
    -------------------------------------------
    designers_hub | 05/10/05 | 1
    designers_hub | 06/10/05 | 5

  6. #6

    Default

    nice. that is better. so how would you do that? i only know very very very basic php mysql scripts, usualy premade ones.

  7. #7

    Join Date
    Jul 2005
    Location
    Melbourne
    Posts
    1,126

    Default

    graham and notb4dinner have the table setup correct.

    each time the date changes, the database adds a new row. excel should be able to import data like this. its just the way you do the data merge.

    as for sql...

    the pull statement will be something like....

    Code:
    SELECT * FROM tableName WHERE date = value
    this breaks down like this. SELECT (words in caps are SQL commands, and other words are variables) * (star being a wildcard) FROM the table (ie Form -a1 (i suggest a better name scheme, such as t_userInfo)) WHERE the date variable is a value.
    this statement will dump all the users who have logged in on that day.

    in the case that you want to further filter this down to a specific user. you will want to do this

    SELECT * FROM tableName WHERE date = value AND username = $user

    this statement is the same as above, but it adds a further refinement of selecting records that only have a certain date, and a certain username associated with them. in this case, you are using the username stored in the variable $user....

    to do an insert. its the same sort of idea.

    the syntax is INSERT INTO tableName (columnNameA, columnNameB, columnNameC) VALUES ($valueForColumnA, valueForColumnB, valueForColumnC)

    or in your case INSERT INTO form-a1 (username, date, count) VALUES ($user, $currentDate, $loginCount)
    _____

    in order for your idea to work, it would just be a matter of inserting the select into an if statement, and the insert into an else if the condition did not ring true....

    ie:

    if ($value = $SQLquery)
    {
    doSomething like insert some date info into this record
    }else{
    create new user function
    }

    remember, the SQL is to create a new record, or update a current one. and a record is any information on a particular row. rows run on the Y axis and columns on the X axis...

    hope this happens....

    cheers

    W2ttsy

    PS: mods, maybe a shift to CreativeMac/Developer would get more interest?

  8. #8

    Default

    how is this?

    i don't think it will work. its pretty bad LOL

    Code:
    $currentdate = date("J-D-M-Y");
    
    $checkuser=true;
    if SELECT * FROM form1a WHERE date = $currentdate AND user = $user_name validationOK=true;
    if (!$checkuser) {
     INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, +1)
      exit;
    }else{
    INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, 1)
    }

  9. #9

    Default

    i think this is better?

    Code:
    $currentdate = date("J-D-M-Y");
    $value = $currentdate $user_name
    $SQLquery = SELECT * FROM form1a WHERE date = $currentdate AND user = $user_name
    
    if ($value = $SQLquery)
    {
     INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, +1)
    }else{
    INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, 1)
    }
    i think i have some syntax errors...

  10. #10

    Join Date
    Jul 2004
    Location
    Gold Coast
    Posts
    166

    Default

    SQL statements need to be stored as strings and sent to the database with the appropriate function calls ( mysql_query() ) the results then need to be retrieved from the result type that the call returns. I'd suggest having a good read of the documentation for the PHP MySQL library and some tutorials, then if you've got any more questions ask away. You'll learn a lot more by doing some research yourself.

    Also:
    Code:
    INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, +1)
    INSERT statements can only create new records not change the values of existing ones. To change values you need to use an UPDATE statement, they have a pretty similar syntax to SELECT statements with the addition of a SET clause to specify the new value(s).
    Code:
    UPDATE form1a SET count = count+1 WHERE user = '$user_name' AND date = $currentdate

  11. #11

    Join Date
    Nov 2004
    Location
    Chasing rainbows
    Posts
    2,238

    Default

    $currentdate = date("Y-M-D");

    $rows = "SELECT count(*) FROM form1a WHERE date = $currentdate AND user = $user_name"

    if ($rows = 0)
    {
    "INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, 1)"
    }
    else
    {
    "UPDATE form1a set count = count + 1 WHERE date = $currentdate AND user = $user_name"
    }



    Use the YMD format for dates as it's easier to keep things ordered. The rest is just a matter of building and executing the SQL strrings.
    Pass the crystal, spread the Tarot, in illusion comfort lies

    http://www.mistfall.com/

  12. #12

    Join Date
    Jul 2005
    Location
    Melbourne
    Posts
    1,126

    Default

    id be doing the increment outside of the SQL statement personally. instead of having
    Code:
    INSERT INTO tableName (columnA, ColumnB, count) VALUES ($dataA, dataB, +1)
    id add in an extra variable and do it like this

    Code:
    $currentdate = date(Ymd); // sets the date to yyyymmdd which is how SQL dates are stored. 
    
    if(stuff here)
    {
    $incCount++;
    UPDATE form1a SET count = incCount WHERE date = $currentdate AND user = $user_name
    } else {
    INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, 1)
    }

    as you can see, i made an incremented variable and then parsed it into the SQL formula. much cleaner that way. its just inserting raw data then, and not trying to change it.
    also, PHP has a date() method, with parameters for the SQL date type so you can just call that to get the current date and assign it to a variable.

    also, you may have to do a little more with the arguments of the if(args) statement. mine was psuedo coded to demonstrate the example. but your method may work, so test it out...

    also, may i suggest you settle on a particular scheme for naming variables and methods? they seem to jump around a bit...

    typically its firstSecondThird or first_second_third... each alphanumeric representing a new word.
    try to use one naming scheme. just makes the code easier to read in the long run.

    good luck

    stealth edit to add in that you may want to look at YourSQL. it has a nice GUI to use, and also that you can test free form SQL and it will tell you if it works or not, and will even show you the output of the statement (if it is valid). which is good for debugging.

    W2ttsy

  13. #13

    Join Date
    Jul 2004
    Location
    Gold Coast
    Posts
    166

    Default

    designers_hub, feel free to ignore this post.

    Originally posted by W2ttsy@Oct 5 2005, 09:15 PM
    id be doing the increment outside of the SQL statement personally.
    Interesting... I'd recomend the exact opposite. By doing the increment in SQL:
    1)You avoid the need to retrieve the count in the first place (=less overhead).
    2)The SQL statement is more representative of the operation you're performing ('increment count' vs. 'set count&#39.

    Generally I try to do as much data manipulation as possible in SQL.

    Then again I think W2ttsy's probably got more experience/education in this than me so make of that what you will...

  14. #14

    Default

    that script looks really nice w2ttsy

    basically it looks like:
    Code:
    include '../config.php';
    include '../opendb.php';
    
    $currentdate = date("Y-M-D");
    
    $rows = "SELECT count(*) FROM form1a WHERE date = $currentdate AND user = $user_name"
    
    if ($rows = 0)
    {
    "INSERT INTO form1a (user, date, count) VALUES ($user_name, $currentdate, 1)"
    }
    else
    {
    "UPDATE form1a set count = count + 1 WHERE date = $currentdate AND user = $user_name"
    }
    the config.php and opendb.php files just hold the info to connect to my database.
    this goes in with a much longer script i made. The entire thing works until i insert that part into it.

  15. #15

    Join Date
    Jul 2005
    Location
    Melbourne
    Posts
    1,126

    Default

    i only suggested it due to global values...

    when ive coded stuff (esp with ASP), ive always needed to call on those values later on. especially if there is another part to the if statement, or if its a nested if statement...

    having a reusable variable is alot better than putting the burdon on the database...

    remember, hit times include updating data, so if your PHP script can take some pressure off the database server, then why not... the small overhead of a single integer increment is going to be way less than running a complex SQL command....

    especially if this script is run by multiple users at one time. you say tomarto, i say tomayto...

    W2ttsy

  16. #16

    Default

    dw i got it...

    i copied the scrip right off the post and i realised you didn't add ";" to the ends

    the pains of programming :o

  17. #17

    Join Date
    Nov 2004
    Location
    Chasing rainbows
    Posts
    2,238

    Default

    Originally posted by W2ttsy@Oct 5 2005, 09:41 PM
    when ive coded stuff (esp with ASP), ive always needed to call on those values later on. especially if there is another part to the if statement, or if its a nested if statement...

    having a reusable variable is alot better than putting the burdon on the database...
    The problem is that PHP is a script so it will have a larger overhead than the ever database will. There's also the issue of data integrity which is far more important than speed. Databases are written to do one thing very well - retrieve and update data - so the more of that type work they can do the better. Global variables in the script can be useful but that increases the chance of data getting out of whack when running simultaneous updates. I know the original poster doesn't need to worry about that but it's a good idea to get into good habits early on.
    Pass the crystal, spread the Tarot, in illusion comfort lies

    http://www.mistfall.com/

  18. #18

    Default

    the php script doesn;t show errors anymore but it doesn't add the record. can someone check over the syntax?

    Code:
    <?php
    include '../config.php';
    include '../opendb.php';
    
    $currentdate = date("Y-M-D");
    
    $rows = "SELECT count(*) FROM form1a WHERE activedate = $currentdate AND user = $user_name";
    
    if ($rows = 0)
    {
    "INSERT INTO form1a (user, activedate, count) VALUES ($user_name, $currentdate, 1)";
    }
    else
    {
    "UPDATE form1a set count = count + 1 WHERE activedate = $currentdate AND user = $user_name";
    }
    ?>
    i tried: But still doesn't add the record.

    Code:
    <?php
    include '../config.php';
    include '../opendb.php';
    
    $currentdate = date("Y-M-D");
    
    $rows = "SELECT count(*) FROM form1a WHERE activedate = '$currentdate' AND user = '$user_name'";
    
    if ($rows = 0)
    {
    "INSERT INTO form1a (user, activedate, count) VALUES ('$user_name', '$currentdate', '1')";
    }
    else
    {
    "UPDATE form1a set count = count + 1 WHERE activedate = '$currentdate' AND user = '$user_name'";
    }
    ?>

  19. #19

    Join Date
    Jul 2005
    Location
    Melbourne
    Posts
    1,126

    Default

    when using connection scripts, i have this nice generic one. works on all sites im making

    Code:
    <?php
    $CONFIG["DBNAME"] = "nameOfDB";
    $CONFIG["DBUSERNAME"] = "root";
    $CONFIG["DBPASSWORD"] = "dbPass";
    $CONFIG["DBADDRESS"] = "127.0.0.1"; // set to 127.0.0.1 which is local host. 
    ?>
    then i just drop this line into all my documents

    require_once("./pathway/to/connScript.php");

    also. what errors are you getting. you can have them printed to the screen. to do this:
    1) locate the php.ini file which is normally located at /usr/local/php4/lib/php.ini
    note, if you have php5 installed (like me) it will be /usr/local/php5/lib/php.ini
    tip: type
    Code:
    locate php.ini
    into the terminal to find it fast
    2) open terminal and type
    Code:
    sudo pico /usr/local/php4/lib/php.ini
    . its important to use sudo so that you get the temperary superuser privledges needed to edit the file.
    3) press control W to find display_errors. then change it from
    Code:
    display_errors = Off
    to
    Code:
    display_errors = On
    4) press control O to write out, and then control X to exit pico.
    5) then type
    Code:
    sudo apachectl graceful
    to restart apache server

    this should help with error detection as errors appear in the browser window when the script is run...

    just adding on to the end of my post. since new stuff was added while i was in the throws of replying...

    you may want to check your SQL using YourSQL. or a similar program...
    im tired so i wont try it tonight, but ill set up a dummy version of this exercise on my machine tomorrow and see if i can find any problems...

    W2ttsy

  20. #20

    Default

    i'm having to problems connecting since i already have a working script for the login page.

    i think its the synax

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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