Asked  7 Months ago    Answers:  5   Viewed   75 times

Say I wanted to do UPDATE table SET name = 'bob' and UPDATE table SET age = 55 WHERE name = 'jim' how do I do them in the same mysql_query()?

EDIT: Since this question has a fair amount of views I'd like to point out that as of PHP 5.5 mysql_query and other mysql_* functions are now deprecated and shouldn't be used.

 Answers

11

I've never tried this, but I think you can use mysqli::multi_query. One of the good things about mysql_query rejecting multiple statements is that it immediately rules out some of the more common SQL injection attacks, such as adding '; DELETE FROM ... # to a statement. You might therefore want to be careful with multiple statements.

Wednesday, March 31, 2021
 
HamidR
answered 7 Months ago
74

From the PHP Manual on mysqli_stmt::execute:

mysqli_stmt::execute -- mysqli_stmt_executeExecutes a prepared Query

Returns TRUE on success or FALSE on failure.


if ($stmt->execute()) { // exactly like this!
    $success = true;
}

You're doing it right... What's your dilemma?

Wednesday, March 31, 2021
 
Sauleil
answered 7 Months ago
71

This is a FAQ for InnoDB tables. See the explanation at https://phpmyadmin.readthedocs.org/en/latest/faq.html?highlight=MaxExactCount#the-number-of-rows-for-innodb-tables-is-not-correct

Saturday, May 29, 2021
 
Student
answered 5 Months ago
28

Jon's answer will work, but IMHO using join in LINQ to Entities is usually wrong, because it duplicates code in your model. I can rewrite Jon's query in a much simpler way in L2E:

var query = from customer in db.Customers
            from order in customer.Orders
            from product in order.Products
            from info in product.Info
            select new
            {
                customer.Name, 
                info.BriefDescription
            }

That's about 50% of the typing and 0% of the duplicated code. Consider that your relationships have already been defined in your DB and in your model. Do you really want to duplicate them again in every query you write, and break your queries when you refactor your model?

Thursday, July 29, 2021
 
Camsoft
answered 3 Months ago
76

When you have to do this sort of thing it is an indicator that your data model is wrong and could do with some fixing.

So, I'd recommend to add a seperate table featured_albums (FK: int id_album) and use that to determine if the album is featured.

Your update becomes

DELETE FROM featured_album; INSERT INTO featured_album SET id_album = $id;

When selecting join the tables

SELECT album.id,
       album.name, 
       ( id_album IS NOT NULL ) AS isfeatured
FROM   album
LEFT JOIN featured_album ON id_album = album.id 

As requested to expand on the above basically I'm suggesting adding a table that will contain a row indicating the currently selected album. This is a 1 to 1 relationship, i.e. one record in the album table has one related record in the feature_albums table. See Types of Relationship.

Album Schema Diagram

You remove the isFeatured field from the album table and add a new table.

CREATE TABLE `featured_album` (
    `id_album` INTEGER NOT NULL,
    FOREIGN KEY (id_album) REFERENCES `album` (`id`)
);

The DELETE FROM .. INSERT INTO line sets the featured album by creating an entry in the table.

The SELECT statement with the LEFT JOIN will pull in the records from the album table and join those that match from the featured_album table, in our case only one record will match so as there is one field in the featured_album table it will return NULL for all records except the featured album.

So if we did

SELECT album.id, album.name, featured_album.id_album as isFeatured0
FROM   album
LEFT JOIN featured_album ON id_album = album.id 

We'd get something like the following:

+----+----------------+------------+
| id | name           | isFeatured |
+----+----------------+------------+
|  1 | Rumours        |       NULL |
|  2 | Snowblind      |       NULL |
|  3 | Telegraph road |          3 |
+----+----------------+------------+

i.e. a NULL for isFeatured or an ID.

By adding the ( id_album IS NOT NULL ) AS isfeatured and using the first query we get

+----+----------------+------------+
| id | name           | isfeatured |
+----+----------------+------------+
|  1 | Rumours        |          0 |
|  2 | Snowblind      |          0 |
|  3 | Telegraph road |          1 |
+----+----------------+------------+

i.e. 0/1 for isfeatured which makes things more readable, although if you're processing the results in PHP it won't make a difference to your code.

Thursday, August 5, 2021
 
Hat
answered 3 Months ago
Hat
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :