MySQL: Setting a variable to a value

This entry is part 21 of 26 in the series Learn MySQL

To set a variable explicitly to a particular value, use a SET statement.

SET syntax can use either := or = to assign the value.

set @price = 5.00;
update artist set price = @price where artiste = 'Rod Stewart'

select * from artist

set

Notice that in SQL, we have always used set to update a value in a table.

MySQL Clients: Using the console

This entry is part 5 of 8 in the series MySQL Clients

MySQL provides a command line console and you can launch it from a path similar to below if you have installed WAMP server in  the C drive.

c:\wamp\bin\mysql5.6.12\bin\mysql.exe

mysql_console

You can also find the console at the mini task bar on the bottom right side of the desktop.

Once you have launched it, you will be prompted to enter the root password.  At the moment, there is no password set for my WAMP server.

PHP: Passing variables from Javascript to PHP

This entry is part 54 of 54 in the series PHP Tutorial

Javascript is a client side programming language and it can’t interact with server side database such as MySQL.

If you are developing HTML 5 apps for Andoid or IOS, you will have to save some inputs into the database.  To store data, you can always pass them into php and store them using PHP.

A common technique is to use AJAX is illustrated by the example below.

ajax.html
<html>
<head>
    <script>
        function submitForm(form)
        {
            var text_input = form.inputbox.value;

            var xmlhttp=new XMLHttpRequest();

            xmlhttp.onreadystatechange=function()
            {
                if (xmlhttp.readyState==4 && xmlhttp.status==200)
                {
                    document.getElementById("text_id").innerHTML=xmlhttp.responseText;
                }
            }            
            xmlhttp.open("GET","get.php?str="+text_input,true);
            xmlhttp.send();
        }
    </script>
</head>

<body>
<form>Enter something in the box: <br><br>
    <input type="text" name="inputbox" value=""><P>
        <input type="button" name="button" value="Click" onClick="submitForm(this.form)">
</form>
<p id="text_id">Input your name to get a response from server</p>
</body>
</html>

get.php:

<?php
$name = $_GET['str'];
echo 'Hello ' . $name . '!';
?>

Put the above 2 files, ajax.html and get.php in the same directory and run ajax.html.

You can try it here.

In the line

xmlhttp.onreadystatechange=function(){}

We put the browser in a ready state to receive a response from the server.

We then send a request to the server using:

xmlhttp.open("GET","get.php?str="+text_input,true);
xmlhttp.send();

The syntax for the method is open(method,url,async)

Method is either in post or get method, we are using get method which is faster and simpler.

async needs to be set to true to enable asynchronous rather than synchronous.

The variable will be passed into the get.php script and store as $name variable.  We can then input the $name variable into MySQL if needed.

PHP MySQL: Outputing data to HTML table I

This entry is part 16 of 17 in the series PHP & MySQL

For HTML table, a basic table would look like this:

<table border="1">
    <tr>
        <th>ID</th>
        <th>artiste</th>
        <th>album</th>
    </tr>
    <tr>
        <td>1</td>
        <td>Rod Stewart</td>
        <td>Maggie May</td>
    </tr>
</table>

We can always start from the above table and built from there.  We can for example, add one more column or one more row to the above table.

For the table that we have used, the HTML will be as below:

<table border="1">
    <tr>
        <th>ID</th>
        <th>artiste</th>
        <th>album</th>
        <th>year</th>
    </tr>
    <tr>
        <td>1</td>
        <td>Rod Stewart</td>
        <td>Maggie May</td>
        <td>1973</td>
    </tr>
    <tr>
        <td>2</td>
        <td>New Order</td>
        <td>Substance</td>
        <td>1987</td>
    </tr>
    <tr>
        <td>3</td>
        <td>Nirvana</td>
        <td>Smell Like Teen Spirit</td>
        <td>1992</td>
    </tr>
    <tr>
        <td>4</td>
        <td>Rod Stewart</td>
        <td>Out Of Order</td>
        <td>1988</td>
    </tr>
</table>

The output from running the above HTML script is:

html_table

PHP Form & MySQL: Getting the date from a form

This entry is part 3 of 3 in the series PHP Form & MySQL

Datetime is a very common data type in database.  There are various functions available in php and mysql for manipulating the datetime variables.

Here, we have a form for us to input the release date of a movie.

<html>
<body>

<form action='' method='post'>
    Movie: <input type='text' name='title'><br>
    Actor: <input type='text' name='actor'><br>
    Release Date yyyy-mm-dd: <input type='text' name='release_date'><br>
    <input type='submit'>
</form>

</body>
</html>
<html>
<body>

<?php
$title = @$_POST['title'];
$actor = @$_POST['actor'];
$release_date = @$_POST['release_date'];

echo "Title: " . $title . "<br>";
echo "Actor: " . $actor . "<br>";
echo "Release Date: " . $release_date . "<br>";


$mysqli = new mysqli("localhost", "root", "");
if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL";
}

$sql_db = "create database if not exists allmovie";
mysqli_query($mysqli, $sql_db);

$sql_use_db = "use allmovie";
mysqli_query($mysqli, $sql_use_db);

$sql_tb = "create table if not exists movie (id int auto_increment primary key, title varchar(30), actor varchar(50), rel_date datetime)";
mysqli_query($mysqli, $sql_tb);

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);


if ($release_date) {
    $release_date = $release_date->format('Y-m-d');
    $sql_in = "insert into movie (title, actor, rel_date) values ('$title', '$actor', '$release_date')";
    mysqli_query($mysqli, $sql_in);
}

mysqli_close($mysqli);

?>

</body>
</html>

dateform

In this example, we have used the Y-m-d format, which is a frequently used format.  After getting the date from the form, we need to convert it to a format that we could insert into mysql.

We have used OOP style as below.  Note that even though we have asked the user to input the date in Y-m-d format, we still need the 2 statements below.

$release_date = DateTime::createFromFormat('Y-m-d', $release_date);
$release_date = $release_date->format('Y-m-d');

qform

There is this line:

$sql_use_db = "use allmovie";

Currently we only have one database.  We could have a few datasases and this line is to let us create the table movie under the database allmovie.