SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    scotland
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    simple php command to modify mysql data

    i'm really quite clueless when it comes to using php and mysql but i'm attempting to learn how to use them both for a personal project i'm working on. my project is a simple browser-based rpg style game.

    i'm struggling to understand and even approach a simple problem...

    how would i go about using php to change a value stored in a mysql database table when i click an on-screen button or a hyperlink.

    now, i'm guessing that something like this is very simple to do, but i don't really know where to even begin. i'd really appreciate all suggestions - please bear in mind though that i am an absolute beginner. i'm really just looking for general advice and suggestions as to the easiest way to do this.

    as i learn more, i'll worry then about making procedures like this efficient and robust. at the moment though, all i'm doing is attempting(but struggling) to do is to use the most basic of commands to understand the basic prinicples.

    thanks in advance for any answers.

  2. #2
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Mojosam!

    First up, you generally don't want a hyperlink click to modify data in your database: clicking a link submits a GET request to the server, which according to the HTTP spec shouldn't modify anything on the server side. This is important if search engine bots or client-side accelerators try to follow those links.

    That said, here's a quick start for you. First you need a form on your web page that includes your button. The HTML:

    Code HTML4Strict:
    <form action="script.php" method="post">
      <button type="submit">Press me!</button>
    </form>

    Next, script.php on the server:

    Code PHP:
    $link = mysqli_connect('localhost', 'root', 'password');
    mysqli_select_db($link, 'my_database');
    $sql = "UPDATE position SET x=23 WHERE id=1;";
    mysqli_query($link, $sql);

    This is obviously a really simple example that isn't getting any info from the client side, and isn't checking for errors so you won't know if something isn't working. So you need to customize it a little, but I just wanted to give you an idea how to connect to your database and run SQL queries with PHP. The first line connects to the MySQL database, with the username and password you set during MySQL installation. Then you select which db you want to use in the second line. Then we set the query you want to run, and run it. This example is kind of silly because it will always run the same query, which is almost never what you want, but I figure it's something for you to play with a little and then you can come back and ask more questions when you're at the next step ;-)

    I'd really recommend getting the free sample of Kevin's book on PHP and MySQL, it's over 150 pages and free, you'll probably learn quite a bit about how to do this kind of thing: http://www.sitepoint.com/books/phpmy...lechapters.php

  3. #3
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    scotland
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi louis

    thanks for your post and please forgive my laziness in responding. i managed to find a copy of kevins book in my local library so hopefully that should be very useful.

    anyway, i looked at what you wrote - it hurt my brain - but i eventually sat down and knocked up some code and this is what i came up with...

    file is called "viewall.php"
    PHP Code:
    <?php

    $dbcnx
    =mysql_connect('localhost''root');
    if (!
    $dbcnx)
        {
        exit(
    'unable to establish connection to database server at this time');
        }
        
    if (!
    mysql_select_db('testdb'))
        {
        exit(
    'unable to locate the database testdb at this time');
        }
    ?>

    you have hurt : 

    <?php

    $result
    =mysql_query('SELECT valueb FROM example');
    if (!
    $result)
        {
        exit(
    'query failed' mysql_error() );
        }
    while (
    $row mysql_fetch_array($result))
        {
        echo 
    $row['valueb'];
        }
    ?>

    mushrooms
        
    <form action="script.php" method="post">
      <button type="submit">brutalise a mushroom</button>
    </form>
    the "script.php" file is...

    PHP Code:
    <?php

    //  this will change the value of "value b" stored in the table called 'example'

    $dbcnx mysql_connect('localhost''root');
    if (!
    $dbcnx)
        {
        exit(
    'unable to connect to database server: ' mysql_error());
        }

    mysql_select_db("testdb"$dbcnx);
    mysql_query('UPDATE example SET valueb=valueb+1');
    ?>

    *** here i am using a hyperlink to return to the viewall.php file shown above ***
    now i know it's all hideously messy but it works, so i'm quite delighted - you have no idea how long it has been since i wrote any lines of code.

    anyway, are there any major flaws in what i've done that i should be aware of? i know i should use a password for mysql but since i have nothing worth breaking or stealing, it doesn't seem necessary right now.

    rather than using a hyperlink to return to the first page(the one with the button) - can you give me a hint as to the best way to keep everything on the same page?

    i know the layout and everything is terrible but figuring out how to make things pretty will be on of the last things i have to learn.

    anyway, thanks for taking the time to read - i hope my ugly code doesn't offend anyone!

  4. #4
    Team ********* Louis Simoneau's Avatar
    Join Date
    Sep 2009
    Location
    Melbourne, Australia
    Posts
    228
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hi Mojosam!

    Looks like a good start :-) Explaining how to link all your pages together and display the right ones is a little more than easily fits into a forum post, but if you already have a copy of Kev's book on hand I'd recommend a thorough reading of chapters 6 and 7 which present a very solid way of organizing your files, including them, and presenting them to your users depending on the action that they're doing.

    Good luck!

  5. #5
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by mojosam View Post
    rather than using a hyperlink to return to the first page(the one with the button) - can you give me a hint as to the best way to keep everything on the same page?
    You can have the form submit to the page it is on by changing the action="" attribute). But be careful, as this would mean a user refreshing the page would be submitting the form again (eg double post on a forum).

    Instead you could put this at the end of script.php, which will redirect the browser back to the form
    PHP Code:
    header('Location: yourformpage.php'); exit; 

  6. #6
    SitePoint Enthusiast
    Join Date
    Oct 2009
    Location
    scotland
    Posts
    39
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you both for your responses - especially hash for that little line of code - i won't even pretend i know how or why it works - all i'm doing right now is trying to figure out the most basic interactions so i'm sure it will be a very useful little shortcut to use. thanks again


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
  •