I have a form into which the visitor can enter data, and I want to store this data in a mysql database via the $_POST variable. What do I need to prevent sql injection?
I'm running lots of cron job and once in a while there is a MYSQL has gone away error.
I've now written some code to handle the error, but how do I simulate the error on my localhost so that the code can be tested thoroughly?
Possible Duplicate:
MySQL ORDER BY DESC is fast but ASC is very slow
Order by DESC is super fast and Order by ASC takes almost 20 times more time.
How could it be caused and how could it be solved?
Hi,
I'm very new to JQuery, but I was told the one of the cool things about it is you can query a mysql database right from an html page(or in my case a smarty template)with out needing php.
I have not found any examples of this so I am asking if someone has one? Thanks
Does it make sense to use UUID as primary key in MySQL?
What would be pros and cons of using UUID instead of regular INT, beside trouble of hand querying?
Hello,
I have a mysql table. It has auto increment on the id. but I regularly delete rows so the numbers are all over the place. I need to get the last n rows out, but because of deletions, the common way of using the max of the autoincremented id column doesn't work well...
1 - Is their another way to get the bottom 50?
2 - Is their a way to get rows by actual row number? so if I have 4 rows labelled 1,2,3,4 delete row 2 then it will become 1,2,3 rather than 1,3,4?
I'm a PHP noob.
I have a database that I will update weekly with a CSV. So far I've managed to upload the file to the server, open the file with PHP, and insert the data in my table.
Now I want to return the unique records that were added (on screen or in a file). How do I do this?
I have a mysql table set up like so:
user_id | document
44 [blob]
44 [blob]
44 [blob]
46 [blob]
I'd like to export all of user_id 44's data to an SQLite3 file.
Best way to go about this without writing a script that reads the data and dumps it into a SQLite file?
Hello guys,
I need some help with a complex SQL query. Here's my setup: there are two tables, one with authors, and another with books. The books table contains a field named "author" which holds author's id. If a book has more than one author, then the "author" field will contain all the authors ids separated by ";" (something like 2;34;234).
On the website I have to list all the books written by an author. If there's only one author, its simple, but how can I get those books where the author I'm interested in is the second or third author?
Many thanks.
Hey there, I have this MySQL query for list a "Vote Ranking" for "Actions" and it work fine, but I want what the "id_user" doesn't repeat, like made a "DISTINCT" in this field.
SELECT
count(v.id) votos,
v.id_action,
a.id_user,
a.id_user_to,
a.action,
a.descripcion
FROM
votes v,
actions a
WHERE
v.id_action = a.id
GROUP BY
v.id_action
ORDER BY
votos DESC
The result:
votes act id_user
3 3 745059251
2 20 1245069513
2 23 1245069513
2 26 100000882722297
2 29 1245069513
2 44 1040560484
2 49 1257441644
2 50 1040560484
The expected result
votes act id_user
3 3 745059251
2 20 1245069513
2 26 100000882722297
2 44 1040560484
2 49 1257441644
2 50 1040560484
Thanks in advanced!
I'm trying to check if a table already exists, however I can't get this working.
IF EXISTS (SELECT 1
FROM sysobjects
WHERE xtype='u' AND name='tablename')
SELECT 'table already exists.'
ELSE
BEGIN
CREATE TABLE Week_(
id INT(10)AUTO_INCREMENT PRIMARY KEY (id),
...
...)
END;
My error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'IF EXISTS (SELECT 1 FROM sysobjects WHERE xtype='u' AND name' at
line 1
Can someone help me with this?
Thanks in advance
I have a a mysql db with myisam engine. Along with many other table I have this table "et" which has around 8137037 records.
I have created indexes (individual index of column hname and pnum, it did not help much later created joint index of hname and pnum and it help execute within a second)such that queries like
"select st from et where hname='name' and pnum='1' limit 1;" execute fast (with in a second) but the problem is I must execute this query "select st from et where hname='name' and pnum='1' order by id limit 1" where id is the primary key of the table and this query sometimes take 145 seconds :(
how can i resolve this issue?
I have problem querying table with variable in IN function.
SELECT
s.date,
(SELECT
GROUP_CONCAT(value)
FROM value
WHERE id_value
IN(s.ref_values)
)
AS vals
FROM stats s
ORDER BY s.date DESC
LIMIT 1
Where s.ref_values is '12,22,54,15'. I get only one return for first number (12).
When I insert that value directly in IN(12,22,54,15) it finds all 4.
So, there must be problem with using variable in IN. What am I doing wrong?
I have a MySQL query which gets including some vars like that:
messages TABLE receiver cols
user1 rows : 1,3,5
user2 rows : 2,3
user3 rows : 1,4
I want to get rows which includes '3' value. So I will get 'user1' and 'user2'.
I tried that but naturally it doesn't work.
mysql_query("SELECT * FROM messages WHERE receiver='3'");
How can I do this?
Two tables:
table_a
-------
table_a_id: (primary, int)
table_b
-------
table_a_id: (index, int, from table_a)
table_b_value: (varchar)
A one to many relationship between table_a_id and table_b_value.
Given a query like this:
SELECT DISTINCT(table_a_id) FROM table_a
JOIN table_b ON table_a.table_a_id=table_b.table_a_id
I want to order by the number of occurrences of table_a_id in table_b. I'm not really sure how to write this in MySQL.
Hi guys, I've got this table
CREATE TABLE `subevents` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(150) DEFAULT NULL,
`content` text,
`class` tinyint(4) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=MyISAM
Each row can have a different value in the 'class' field.
I'd like to select any number of rows, ordered randomly, as long as the sum of the values in the 'class' field is equal to 100.
How could I accomplish it directly in the MySQL query without doing it later in PHP?
Thanks everybody!
Hi,
Is this possible, can a trigger in mysql can do a select first and then based on its result do a delete?, both on the same table.
Am struggling to get it right.
There are duplicate entries in a table, i need to have a trigger which selects and then deletes.
Any ideas or thoughts will be really helpful.
In my MySQL database, I have a float field named "HoursSpent" with values like "0.25", "1.75", "2.5", etc. Is there a way that I can have my SELECT statement format those values in a friendly format like this?:
0.25 = 15 minutes
1.75 = 1 hour and 45 minutes
2.5 = 2 hours and 30 minutes
The "HoursSpent" field is supposed to only have values in 0.25 increments, but if somebody were to put something random like 0.16, it would be nice if the SELECT statement handled that by rounding it up to the nearest 0.25 (so in this case 0.16 would become 0.25, or 15 minutes).
I'm starting to develop an application using MySQL and although I've developed apps before using databases, I've normally gone to the incrementing id method. The other day I was reading a posting somewhere and saw someone being picked apart for this, for not properly "normalising the database". I'm not a big database person, but I wanted to make sure I'm doing this right.
Any ideas, help / guidance?
Yes it's Windows sorry.
I'm using mysqldump with the option -T which creates a sql and a txt file per table.
mysqldump -u user -ppass db -T path
I use that option to be able to restore easily one table.
Now I'd like to restore all the tables.
mysql -u user -ppass db < path/*.sql
Obvously doesn't work
Also, I don't know where do my funcs/procs go.
Thx
Lets say I'm starting a site that users can enter content but all my categories are empty except one or two, how should the empty categories be displayed to my users if at all using MySQL & PHP?