When use of back ticks in table name solves my problem in PHP/mysql

While playing with the code today, I found an interesting benefit of using back ticks in the table name in mysql query.

I created a table in my database as “abc.test” and added some dummy data to the table as shown in below screenshot.

table with a dot

Now with a simple mysql_query I was trying to fetch the data but its returning nothing, after a check I found that i missed those back ticks from my table name.

Incorrect Query (without back ticks)

mysql_query(“SELECT * FROM abc.test”);

Correct Query (with back ticks)

mysql_query(“SELECT * FROM `abc.test`”);

My whole working php code, to fetch data –

$conn = mysql_connect('localhost','root','');
if (!$conn)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db('stcktest', $conn);

$result = mysql_query("SELECT * FROM `abc.test`");

//print_r(mysql_fetch_assoc($result));

while ($row = mysql_fetch_assoc($result) )
{
echo "Id is : ".$row['id']." "." Name is : ".$row['name'];
echo "
";
}

Advertisements

Search and replace within a column in mysql

In my codeigniter project I need to add slug field in the database from name column. This is what i did –

First of all i created a field name “slug” just after ename column and copied that content of ename to slug, using this query-

UPDATE default_event
SET `slug` = `ename`;

Then i fire this query to replace blank spaces with “-” to form links-

UPDATE default_event
SET slug = replace(slug, ‘ ‘, -‘);

How to apply two inner joins within a single query

This is the query i run to fetch data from multiple tables —

SELECT qtq.c_ques_cat AS q_cat, qtq.c_point AS fuly_score, SUM( qrs.c_score ) AS us_score, L.qc_category AS q_category
FROM jos_quiz_r_student_question AS qrs
INNER JOIN jos_quiz_t_question AS qtq ON qrs.c_question_id = qtq.c_id
INNER JOIN jos_quiz_q_cat AS L ON L.qc_id = qtq.c_ques_cat
AND qrs.c_stu_quiz_id = '784'
AND qtq.published =1
GROUP BY q_cat
ORDER BY us_score
LIMIT 0 , 30

Brace privilege in ADD and MODIFY in mysql

My Problem

table - tbl_customer 
tool- phpmyadmin

Queries —

ALTER TABLE tbl_customer    <-- All Good
ADD( price int(10) NOT NULL);

While —-

ALTER TABLE tbl_customer   <-- Not Working
MoDIFY( price float(12,2) NOT NULL);

however this works correctly without braces –

ALTER TABLE tbl_customer   <-- All Good
MoDIFY price float(12,2) NOT NULL;


REASON ----------

ADD allows more than 1 column to add but modify allows only single, so braces been provided for add not for modify.