Updating meta value in database mysql

[elyfrank]

Hi guys,

I have a problem with my code.

It connects with the database but it doesn't execute anything.

I am trying to update a value in a table but I really don't know what I am doing wrong.

I am trying to change the price based on the meta_value GF-1370

Thank you very much.

 

Here is Table (Taken from myphpadmin)

meta_id  post_id  meta_key  meta_value

18538 4356 _sku GF-1370 18541 4356 _price 2.343

 

and here is my code:

 <?php 
$link = mysql_connect('server', 'username', 'password'); 
if (!$link) { 
    die('Could not connect: ' . mysql_error()); 
} 
echo 'Connected successfully'; 
mysql_select_db(wordpress_i12dhflgoa);
$sql="update `wp_pzvy_postmeta` set `_price` = 5 WHERE `_sku` = 'GF-1370'";
?>  

[QuickOldCar]

You now need to execute the query

mysql_query()

[elyfrank]

Hi,

I changed the code a bit and tried to execute it and I am getting this error: Error updating record: Unknown column '_sku' in 'where clause'

Here is the code

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
 $sql="update `wp_pzvy_postmeta` set `_price` = 5 WHERE `_sku` = 'GF-1370'";

if (mysqli_query($conn, $sql)) {
    echo "Record updated successfully";
} else {
    echo "Error updating record: " . mysqli_error($conn);
}
mysqli_close($conn);

[elyfrank]

This is the table from myphpadmin. It looks like it got reformatted when I originally posted.

 

meta_id        post_id         meta_key                meta_value

18538            4356             _sku                          GF-1370

18541            4356             _price                        2.343

[TOA]

I hate the way wordpress does tables, but I digress...You'll need to use the post_id to update the price, price is not actually in that record

 

set meta_value = '5' where meta_key = '_price' AND post_id='4356'

Edited November 12, 2014 by TOA

[elyfrank]

How can I get the post_Id based on the meta_value? In this case meta_value is GF-1370?

Thank you

[TOA]

Wordpress provides this to you in the loop as the_id() I think, but double check that.

 

But to answer your question, here's how you'd do it:

set meta_value = '5' where meta_key = '_price' AND post_id=(select post_id from postmeta where meta_key = '_sku' and meta_value = 'GF-1370')

 

But use the post_id wordpress gives you, it's a much cleaner query

[elyfrank]

The thing is that I am doing this out of wordpress, it is a separate script running in the server.

Hey,

Thank you very much for all your help, I will try this out this afternoon and let you know if everything works ok.

[elyfrank]

I get this error: Error updating record: Table 'wordpress_i12dhflgoa.postmeta' doesn't exist

 

Here is the code I am using:

$sql="update `wp_pzvy_postmeta` set meta_value = '10' where meta_key = '_price' AND post_id=(select post_id from postmeta where meta_key = '_sku' and meta_value = 'GF-1370')";

[Barand]

Which bit of that error message don't you understand?