In class, we are all ‘studying’ databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using Access. I have managed to create databases and tables, but now how do I make a relationship between…
Tag: mysql
How do you OR two LIKE statements?
I have tried the following two statements: SELECT col FROM db.tbl WHERE col (LIKE ‘str1’ OR LIKE ‘str2’) AND col2 = num results in a syntax error SELECT col FROM db.tbl WHERE page LIKE (‘str1’ OR ‘…
What is equivalent of the Nz Function in MS Access in MySQL? Is Nz a SQL standard?
What is MySQL equivalent of the Nz Function in Microsoft Access? Is Nz a SQL standard? In Access, the Nz function lets you return a value when a variant is null. Source The syntax for the Nz function is: Answer The COALESCE() function does what you describe. It’s standard SQL and it should be supported …
Can you use an alias in the WHERE clause in mysql?
I need to use an alias in the WHERE clause, but It keeps telling me that its an unknown column. Is there any way to get around this issue? I need to select records that have a rating higher than x. Rating is calculated as the following alias: Answer You could use a HAVING clause, which can see the aliases,
How to use GROUP BY to concatenate strings in MySQL?
Basically the question is how to get from this: foo_id foo_name 1 A 1 B 2 C to this: foo_id foo_name 1 A B 2 C Answer https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL v…
Loading .sql files from within PHP
I’m creating an installation script for an application that I’m developing and need to create databases dynamically from within PHP. I’ve got it to create the database but now I need to load in …
Is there something wrong with joins that don’t use the JOIN keyword in SQL or MySQL?
When I started writing database queries I didn’t know the JOIN keyword yet and naturally I just extended what I already knew and wrote queries like this: Now that I know that this is the same as an INNER JOIN I find all these queries in my code and ask myself if I should rewrite them. Is there something…
A script to change all tables and fields to the utf-8-bin collation in MYSQL
Is there a SQL or PHP script that I can run that will change the default collation in all tables and fields in a database? I can write one myself, but I think that this should be something that readily available at a site like this. If I can come up with one myself before somebody posts one, I will
How can I prevent SQL injection in PHP?
If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST[‘user_input’]; …