MySQL about constraint

Question 1

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.

Answer

Starx

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
)

Question 2

Haren’s Question:

haren

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

Answer

Starx

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.

Question 3

Agus Maloco’s Question:

Agus Maloco

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?

Answer

Starx

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
);

Question 4

Ehsan’s Question:

Ehsan

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.

Answer

Starx

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

Question 5

Sunny’s Question:

sunny

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

Answer

Starx

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.

Question 6

Kirzilla’s Question:

Kirzilla

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

Thank you.

Answer

Andy

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.

Answer

Starx

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]

Question 7

User2827404’s Question:

user2827404

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>`

Answer

Starx

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'];

Question 8

Tompa’s Question:

Tompa

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)

Answer

Starx

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;

Question 9

Three3’s Question:

three3

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?

Answer

Starx

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' />

Question 10

Adura’s Question:

Adura

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.

Answer

Milan Zavišić

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;

Answer

Starx

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'

Tag: mysql

MySQL about constraint

J.Doe’s Question:

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

Haren’s Question:

How to create time attribute in SQL?

Agus Maloco’s Question:

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

Ehsan’s Question:

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

Sunny’s Question:

How to get available values for SET field?

Kirzilla’s Question:

Log in script load users name

User2827404’s Question:

Sort by AVG(rating)

Tompa’s Question:

Using mod_rewrite to make Blog URLs more SEO Friendly

Three3’s Question:

Another Technique

MySQL Select columns which has two matches in other table

Adura’s Question:

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