insert incremental values in an insert ... select statement mysql
Tag : php , By : jazzyfox
Date : March 29 2020, 07:55 AM
To fix this issue I am trying to select a number of rows in a table, reverse the values in one column and reinsert them into the table. Here is an example of what I am doing, say I have the following data: , try this INSERT INTO myTable(`porder`, x, y) SELECT (SELECT MAX(`porder`) FROM myTable) - `porder`, x, y FROM myTable
|
MySQL INSERT [...] SELECT with mixed data (from SELECT clause and hand-inserted values)?
Tag : mysql , By : suresh
Date : March 29 2020, 07:55 AM
around this issue I'm trying to modify a table from OpenCart, a PHP-based e-cart solution. I currently have the oc_product_to_store table populated with the 1300+ product catalog we currently offer. We created a second store and I want to update that table for every product_id to have a second row pointing to the second store (so, in practical reality, I want to insert the 1 value into the store_id field for each new row, while preserving the product_id value). , So you mean? INSERT oc_product_to_store (productid, storeid)
SELECT productid, 2
FROM oc_product_to_store
WHERE storedid=1
|
mysql select to insert two values from one specific row and plus two values (paired) from some random row
Tag : mysql , By : Naveen
Date : March 29 2020, 07:55 AM
this one helps. I think you were pretty close. In this case rather than getting caught up in the joins (except for the first one you needed) and the subqueries you can use simple variable assignment and a UNION clause. The only other thing missing that was causing an issue was that you need to use INSERT INTO...SELECT syntax vs. INSERT INTO...VALUES syntax ( Reference). INSERT INTO friends (user, friend)
SELECT @user1:=users.user as user1, invited.user as user2
FROM users
INNER JOIN invited
ON users.email=invited.invitee
WHERE invited.invitee = 'baz@bar.com'
UNION ALL
(SELECT @user1 as user1, user
FROM users
ORDER BY RAND() LIMIT 2)
|
Using INSERT INTO SELECT and VALUES MYSQL PDO
Tag : php , By : robinator
Date : March 29 2020, 07:55 AM
To fix this issue After a lot of googling and running around the different questions in StackOverflow, I haven't really found a solution that solves my problem. , You said: <?php
/*
* ============================================================
* Set error reporting level and display errors on screen.
* Use it ONLY ON A DEVELOPMENT SYSTEM, NEVER ON PRODUCTION!
* If you activate it on a live system, then the users will see
* all the errors of your system. And you don't want this!
* ============================================================
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
try {
// Read needed variables.
// TODO: Provide your values.
$user_cart_table_name = 'user_cart_table_name';
$user_name = 'user name value';
$order_name = 'order name value';
$order_address = 'order address value';
$order_phone = 'order phone value';
// Create a PDO instance as db connection.
// TODO: Delete this and use your own connection.
// But use the first two driver options that I defined here as
// the options on your connection.
$conn = new PDO(
'mysql:host=localhost;port=3306;dbname=mydb;charset=utf8'
, 'myuser'
, 'mypass'
, array(
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_EMULATE_PREPARES => FALSE,
PDO::ATTR_PERSISTENT => TRUE,
)
);
/*
* The sql statement - it will be prepared.
*
* Familiarize yourself with the sprintf() function.
* It is your very good friend when building complex
* sql statements.
*/
$sql = sprintf('INSERT INTO user_orders (
order_item_id,
order_item,
order_quantity,
order_user,
order_name,
order_address,
order_phone
)
SELECT
item_ID,
item_name,
item_quantity,
user_name,
"%s",
"%s",
"%s"
FROM
%s,
user_main
WHERE
item_status = "carted"
AND user_name = :user_name'
, $order_name
, $order_address
, $order_phone
, $user_cart_table_name
);
// Prepare the sql statement.
$stmt = $conn->prepare($sql);
// Bind the input parameters to the prepared statement.
$bound = $stmt->bindValue(':user_name', $user_name, PDO::PARAM_STR);
// Execute the prepared statement.
$executed = $stmt->execute();
// Get the last insert id.
$lastInsertId = $conn->lastInsertId();
// Display last insert id.
echo 'Record added with id ' . $lastInsertId;
// Close connection.
$conn = NULL;
} catch (PDOException $exc) {
echo $exc->getMessage();
exit();
} catch (Exception $exc) {
echo $exc->getMessage();
exit();
}
|
How to Insert SELECT ALL values or SELECTED checkboxes values into different columns in mysql?
Tag : php , By : user170635
Date : March 29 2020, 07:55 AM
may help you . Before inserting into mysql check if the value is exist in $_POST['addbook'] do this for all the checkboxes. You can also use array of checkbox. <?php
// Do this for rest of the values also.
if (isset($_POST['addbook'])){
$addbook=$_POST['addbook'];
}else{
$addbook = "default value"
}
?>
|
Boards Message :
Related QUESTIONS :