October 24, 2016

MySQL about constraint

J.Doe’s Question:

Good afternoon,

Can anyone tell me what’s wrong with my code on PHP MY ADMIN, I’m trying to write a CONSTRAINT and create values for the car color in the beginning ( with table creation)

CREATE TABLE T_CAR
(CAR_ID                INTEGER       NOT NULL PRIMARY KEY,
 CAR_MARK            CHAR(32)      NOT NULL,
 CAR_MODEL            VARCHAR(16),
 CAR_NUMBER   CHAR(10)      NOT NULL,
 CAR_COLOR           CHAR(16)      CHECK (VALUE IN ('white', 'black', 'red', 'green', 'blue')))

The problem is with the last line (error message syntax not known).
Thanks in advance.

MySQL ignores check expression.

Manual: Create Table

The CHECK clause is parsed but ignored by all storage engines.

Try Enum:

CREATE TABLE T_CAR (
    CAR_ID INTEGER NOT NULL PRIMARY KEY,
    CAR_MARK CHAR(32) NOT NULL,
    CAR_MODEL VARCHAR(16),
    CAR_NUMBER CHAR(10) NOT NULL,
    CAR_COLOR ENUM('white', 'black', 'red', 'green', 'blue') NOT NULL
)
September 20, 2016

How do I know when my docker mysql container is up and mysql is ready for taking queries?

Haren’s Question:

I am deploying a few different docker containers, mysql being the first one. I want to run scripts as soon as database is up and proceed to building other containers. The script has been failing because it was trying to run when the entrypoint script, which sets up mysql (from this official mysql container), was still running.


sudo docker run --name mysql -e MYSQL_ROOT_PASSWORD=MY_ROOT_PASS -p 3306:3306 -d mysql
[..] wait for mysql to be ready [..]
mysql -h 127.0.0.1 -P 3306 -u root --password=MY_ROOT_PASS < MY_SQL_SCRIPT.sql

Is there a way to wait for a signal of an entrypoiny mysql setup script finishing inside the docker container? Bash sleep seems like a suboptimal solution.

EDIT: Went for a bash script like this. Not the most elegant and kinda brute force but works like a charm. Maybe someone will find that useful.


OUTPUT="Can't connect"
while [[ $OUTPUT == *"Can't connect"* ]]
do
OUTPUT=$(mysql -h $APP_IP -P :$APP_PORT -u yyy --password=xxx < ./my_script.sql 2>&1)
done

On your ENTRYPOINT script, you have to check if you have a valid MySQL connection or not.

This solution does not require you to install a MySQL Client on the container and while running the container with php:7.0-fpm running nc was not an option, because it had to be installed as well. Also, checking if the port is open does not necessarily mean that the service is running and exposed correctly. [more of this]

So in this solution, I will show you how to run a PHP script to check if a MySQL Container is able to take connection. If you want to know why I think this is a better approach check my comment here.

File entrypoint.sh

#!/bin/bash
cat << EOF > /tmp/wait_for_mysql.php
<?php
$connected = false;
while(!$connected) {
    try{
        $dbh = new pdo( 
            'mysql:host=mysql:3306;dbname=db_name', 'db_user', 'db_pass',
            array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)
        );
        $connected = true;
    }
    catch(PDOException $ex){
        error_log("Could not connect to MySQL");
        error_log($ex->getMessage());
        error_log("Waiting for MySQL Connection.");
        sleep(5);
    }
}
EOF
php /tmp/wait_for_mysql.php
# Rest of entry point bootstrapping

By running this, you are essentially blocking any bootstrapping logic of your container UNTIL you have a valid MySQL Connection.

April 1, 2016

How to create time attribute in SQL?

Agus Maloco’s Question:

I tried to create a table this way:

create table attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      date_attendance date not null, 
      start_time timestamp 'HH24:MI:SS', 
      finish_time timestamp 'HH24:MI:SS'
);

Am I right about creating the time fields this way or there is some better option?

Timestamp will hold both date and time. There is data type time as well, which might be better suited for your use case.

CREATE TABLE attendance (
      userId char(10) primary key not null, 
      name varchar(35) not null, 
      date_attendance date not null, 
      start_time time,
      finish_time time
);
March 31, 2016

Update schema for create longtext field on MySQL data base on symfony

Ehsan’s Question:

I want to update MySQL field from text to longtext using Doctrine schema.

Now my code is like this:

/**
 *@var string
 *@ORMColumn(name="head_fa", type="string", length=1000, nullable=true)
 */
private $head_fa;

/**
 *@var string
 *@ORMColumn(name="head_en", type="string", length=1000, nullable=true)
 */
private $head_en;

/**
 *@var string
 *@ORMColumn(name="body_fa", type="text", length=1000, nullable=true)
 */
private $body_fa;

/**
 *@var string
 *@ORMColumn(name="body_en", type="text", length=1000, nullable=true)
 */
private $body_en;

and the problem is when i change this field to this code

/**
 *@var string
 *@ORMColumn(name="head_fa", type="string", length=1000, nullable=true)
 */
private $head_fa;

/**
 *@var string
 *@ORMColumn(name="head_en", type="string", length=1000, nullable=true)
 */
private $head_en;

/**
 *@var string
 *@ORMColumn(name="body_fa", type="text", nullable=true)
 */
private $body_fa;

/**
 *@var string
 *@ORMColumn(name="body_en", type="text", nullable=true)
 */
private $body_en;

and run “php app/console doctrine:schema:update –force” command on console it said that “Nothing to update – your database is already in sync with the current entity metadata.” How to change this field to longtext on mysql database.

I do the same on different part of the project.
this is the code

/**
 * @ORMColumn(name="body", type="text", nullable=true)
 */
protected $body;

and after executing the “php app/console doctrine:schema:update –force” command on terminal this field is changed to longtext on MySQL database.

If you don’t specify a length parameter, it will automatically the column as LONGTEXT in MySQL.

June 30, 2015

How Can i copy a mysql record and save with different id using PHP?

Sunny’s Question:

I am new in PHP. I have an idea. Can i copy a recoed of table and save it with different id in same table?

For example i have a web form with different fields. I use that form to store data in database. Now i have a page where i display that record and use CRUD operations. When user click on Edit Button it goes on Form where he see a Button of Create copy. When user click on Create Copy button it just begin to start making a copy of selected data and store same data with different id?

create copy

Here is a simple way. You can have multiple submit buttons. Like

<input type="submit" name="submit" value="Edit" />
<input type="submit" name="submit" value="Make a copy" />

When this forms get submitted, you can check which submit button was pressed by asserting with $_POST['submit'] or $_GET['submit'] if you method is GET.

For example:

if($_POST['submit'] == 'Make a copy') {
    $action = "copy";
} elseif($_POST['submit'] == 'Edit') {
    $action = "edit";
}

Using that you can know what the user wanted to do. Since you already have the data, just pass those to your function which creates a new record without the primary key.

December 9, 2013

How to get available values for SET field?

Kirzilla’s Question:

Is there any way to get available values for SET field in table?

Thank you.

You can retrieve the possible values for a SET field using DESCRIBE myTableName mySetColumn or SHOW COLUMNS FROM myTableName LIKE mySetColumn:

  mysql> DESCRIBE myTableName mySetColumn;
  +-------+-------------------------------------------+------+-----+---------+-------+
  | Field | Type                                      | Null | Key | Default | Extra |
  +-------+-------------------------------------------+------+-----+---------+-------+
  | myset | set('Travel','Sports','Dancing','Dining') | YES  |     | NULL    |       |
  +-------+-------------------------------------------+------+-----+---------+-------+

Informative article here, manual here.

Here is how to get the possible values of SET using PDO extension.

function get_set($table, $column)
{

    global $db; //PDO DB Handler

    $sql = "SHOW COLUMNS FROM $table LIKE :column";
    $stmt = $db -> prepare($sql);

    $stmt -> bindParam(":column", $column, PDO::PARAM_STR, 50);

    try {
        $result = $stmt -> execute();
        $row = $stmt -> fetch(PDO::FETCH_ASSOC);
        $set = $row['Type'];
        $set  = substr($set,5,strlen($set)-7);
        // Split into an array.
        return preg_split("/','/",$set);
    } catch (PDOException $e) {
        echo $e -> getMessage();
        return false;
    }

}

[Source]

September 29, 2013

Log in script load users name

User2827404’s Question:

ive just built a php and mysql log in script which forwards to a members area. I now want the members name that loged in to be displayed, somthing like welcome Stephen for example.

what would be the best way to do this?

ok this is my code once the submit button has been pressed:

`<?php

$host="localhost"; // Host name
$username="stephen2_phptest"; // Mysql username
$password="********"; // Mysql password
$db_name="stephen2_phptest"; // Database name
$tbl_name="registers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

// username and password sent from form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];


// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);

$sql="SELECT * FROM registers WHERE email='$myusername' and password='$mypassword'";
$result=mysql_query($sql);

// Mysql_num_row is counting table row
$count=mysql_num_rows($result);

// If result matched $myusername and $mypassword, table row must be 1 row

if($count==1){

// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword");
header("location:members.php");
}
else {
echo "Wrong Username or Password";
}
?>`

And this is the code for the members area:

`<?php

$host="localhost"; // Host name
$username="stephen2_phptest"; // Mysql username
$password="********"; // Mysql password
$db_name="stephen2_phptest"; // Database name
$tbl_name="registers"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
session_start();
if(!session_is_registered(myusername)){
header("location:index.php");

$myusername=$_POST['myusername'];
}
?>
<html>
<link rel="stylesheet" type="text/css" href="../php/css/styles.css">
<body>
    <div class="members-screen">
Login Successful</br>
Welcome [persons name to load here]<?php echo $_POST['myusername'] ?> <a href="../php/logout.php"> | Logout</a>
<div class="menu">
    <div class="menu-btn">
        <a href="">Home</a>
        </div>
        <div class="menu-btn">
            <a href="">Search</a>
            </div>
            <div class="menu-btn">
                <a href="">Messages</a>
                </div>
                <div class="menu-btn">
                    <a href="">Matches</a>
                    </div>
                    <div class="menu-btn">
                        <a href="">My Account</a>
                        </div>
    </div>
</div>
</body>
</html>`

Store the name on the session when the user logs in and use it when it is needed to be showed.

session_start();

// Your login process

if($valid == true) {
   $_SESSION['logged_user'] = 'stephen'; // Fetch name from database
}

Then where you need to show:

session_start();
echo $_SESSION['logged_user'];
September 15, 2013

Sort by AVG(rating)

Tompa’s Question:

I am trying to write a mySQL-query that sorts by first suburb and then AVG(rating_table.rating).

Here is the street_table:

id       street_name       suburb

0        streetone         subone
1        streettwo         subthree
2        streetthree       subthree
3        streetfour        subtwo

And here is the rating_table:

street_id    rating

1            1
2            1
3            4
2            2
1            3

And this is the result I am looking for:

id      suburb         avarage_rating

0       subone         (no rating)
1       subtwo         1 + 3 / 2 = 2
3       subthree       4 / 1 = 4 (Just one vote..)
2       subthree       2 + 1 / 2 = 1.5

(As you can see, #3 is before #2 because of the avarage_rating)

You can combine the ORDER BY to use multiple columns like:

SELECT .... ORDER BY suburb, AVG(rating_table.rating);

You can define order specific to items too

SELECT .... ORDER BY suburb ASC, AVG(rating_table.rating) DESC;
September 12, 2013

Using mod_rewrite to make Blog URLs more SEO Friendly

Three3’s Question:

I am trying to turn my blog URLs into a more SEO friendly format using mod_rewrite. All of my articles are stored in a simple MySQL database. Each blog article url looks like this:

http://www.test.com/blog?id=20&category=online%20php%20tutorials&pagename=how%20to%20customize%20functions

I have managed to to make them look like this using mod_rewrite:

http://www.test.com/blog/online-php-tutorials/how-to-customize-functions/20

Here is my code that I paced in my .htaccess file:

RewriteRule ^blog/([a-z0-9-]+)/([a-z0-9-]+)/([a-z0-9-]+)/?$ /blog?id=$3&category=$1&pagename=$2 [L]

So what happens is this: When I click on the URL http://www.test.com/blog/online-php-tutorials/how-to-customize-functions/20, all of my CSS and images are not loading because it is trying to load them from a directory that does not actually exists. How would I load the files without having to create multiple directories that contain my sites CSS files and images?

Use root identifier / in your path. This will point the DocumentRoot of your server. Let me explain How this works

For an image like this:

<img src='test.jpg' /> 

Browser/Server will find it as http://www.test.com/blog/online-php-tutorials/how-to-customize-functions/20/test.jpg' but if you use / before the path

<img src='/test.jpg' />

It will look for it in http://www.test.com/test.jpg

Another Technique

Use full path in your files like:

<img src='http://test.com/test.jpg' />
September 10, 2013

MySQL Select columns which has two matches in other table

Adura’s Question:

I am sorry if this question was already solved, but I did not know how to word my problem properly or what I should search for.

So I have 2 tables:

groups

id | name

memberships

id | user_id | group_id

What I am trying to do is find all groups which the user with id 1 is a member of and also user with id 2 is a member of.
Obviously that does not work:

SELECT groups.id FROM groups, memberships WHERE groups.id = memberships.group_id AND memberships.user_id = 1 AND memberships.user_id = 2;

I hope you understand my issue, I am having trouble finding the right words for the problem. Feel free to ask.

Edit: Both users should be a member of the group.

If I understood well, you need groups where both users are members?

Something like:

SELECT g1.id 
FROM groups g1, memberships m1, groups g2, memberships m2
WHERE m1.group_id = g1.id AND m1.user_id = 1
AND m2.group_id = g2.id AND m2.user_id = 2
AND g1.group_id = g2.group_id;

Try this:

SELECT * 
FROM   groups g 
       INNER JOIN memberships m 
               ON m.group_id = g.id 
WHERE  m.user_id = '1' 
        OR m.user_id = '2' 
...

Please fill the form - I will response as fast as I can!