PHP MySQL update database

Question

Member #921280

13 Years Ago

I am new to php and mysql I want to crate a query that does the update or adds a new table if it is not in database.
I get on else for echo: Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home2/public_html/shop/dokumenti/up2.php on line 53 ID 1 ne postoji u drugoj tablici i trenutno: 0 –

And I cannot insert a querry

Can you help me with this code:

        <?php


    $mysql_db = "";
    $mysql_user = "";
    $mysql_pwd  = "";


        $con = mysql_connect("localhost", $mysql_user, $mysql_pwd);
        if (!$con) {
            die('Could not connect: ' . mysql_error());
        }

        mysql_select_db($mysql_db, $con);

          $countUpdated=0;

          //popravak kategorija
            mysql_query("UPDATE ps_product_shop prod_shop INNER JOIN ps_product prod USING(id_product) SET prod_shop.id_category_default = prod.id_category_default WHERE prod_shop.id_product BETWEEN 1 AND 62226");


                    // sve na kunu
                    mysql_query("UPDATE ps_product_supplier SET id_currency = 3 WHERE id_currency = 0");


                    //dodavanje poreza

                    mysql_query("UPDATE ps_product SET id_tax_rules_group = 1 WHERE id_tax_rules_group = 0");
                    mysql_query("UPDATE ps_product_shop SET id_tax_rules_group = 1 WHERE id_tax_rules_group = 0");



        //$kveri = "SELECT id_product_supplier, id_product, id_product_attribute, id_supplier,product_supplier_reference, product_supplier_price_te, id_currency FROM ps_product";
        $kveri = "SELECT id_product,id_supplier,supplier_reference, wholesale_price FROM ps_product";

        $ispis = mysql_query($kveri) or die(mysql_error());

                   while ($row = mysql_fetch_array($ispis)){


                   $trenutnired = $row['id_product'];
                   $trenutnired1 = 0;
                   $trenutnired2 = $row['id_supplier'];
                   $trenutnired4 = $row['supplier_reference'];
                   $trenutnired5 = $row['wholesale_price'];
                   $trenutnired6 = 3;



                                                                //echo $trenutnired;

                $drugatab = "SELECT * FROM ps_product_supplier WHERE id_product = '$trenutnired'";
            if($rezultati = mysql_query($drugatab) && mysql_num_rows($rezultati)){
                // Successful query...
                //mysql_query("INSERT INTO ps_product_supplier (id_product_supplier, id_product, id_product_attribute, id_supplier, product_supplier_reference, product_supplier_price_te, id_currency)  VALUES ('', '$trenutnired', '$trenutnired1',  '$trenutnired2', '$trenutnired4', '$trenutnired5', '$trenutnired6')");
                $countUpdated++;
            } else {
                echo "ID $trenutnired ne postoji u drugoj tablici i trenutno: $countUpdated<br />";
            }
            }


        ?>

mysql php

Edited 13 Years Ago by Member #921280 because: z

2 Contributors
13 Replies
303 Views
2 Days Discussion Span
Latest Post 13 Years Ago Latest Post by Member #921280

Dani AI

Generated 9 Months Ago

The root problems in this thread are a logic/flow bug in the PHP check (the result resource must be assigned before calling mysql_num_rows) and doing a one-query-per-row loop when a single SQL statement will do the job far more reliably and much faster. correctly pointed out the assignment issue; later clarified the goal is to list missing product rows from ps_product_supplier and insert them from ps_product.

A safe workflow (no PHP loop) is:

Run a LEFT JOIN SELECT to verify which ps_product rows lack a matching ps_product_supplier row.
When satisfied, run a single INSERT ... SELECT to add the missing rows in one operation.
Always backup before changing data and test on a copy or small LIMIT batch first.

Dry-run (list missing rows)

SELECT p.id_product, p.id_supplier, p.supplier_reference, p.wholesale_price
FROM ps_product AS p
LEFT JOIN ps_product_supplier AS s ON p.id_product = s.id_product
WHERE s.id_product IS NULL;

Insert missing rows (example mapping; let auto-increment create the supplier PK)

INSERT INTO ps_product_supplier
  (id_product, id_product_attribute, id_supplier, product_supplier_reference, product_supplier_price_te, id_currency)
SELECT p.id_product, 0, p.id_supplier, p.supplier_reference, p.wholesale_price, 3
FROM ps_product AS p
LEFT JOIN ps_product_supplier AS s ON p.id_product = s.id_product
WHERE s.id_product IS NULL;

Additional practical notes:

Do not supply the auto-increment primary key value in the INSERT; omit it and let MySQL assign it.
Run the SELECT first and verify counts: SELECT COUNT(*) FROM ... WHERE s.id_product IS NULL.
For very large batches, run the INSERT inside a transaction or in smaller batches, and ensure an index on ps_product_supplier.id_product for speed.

Enable error reporting while debugging (for PHP):

ini_set('display_errors',1);
error_reporting(E_ALL);

Consider using INSERT IGNORE or ON DUPLICATE KEY UPDATE if duplicate-key behavior must be handled; and migrate from deprecated mysql_* to mysqli or PDO for safety and future compatibility.

This approach matches the intent expressed by and uses the assignment fix suggested by , while avoiding per-row PHP queries and the inverted existence logic that produced misleading output.

All 13 Replies

Member #120589

13 Years Ago

The error probably points to an invalid query. Check your table and field names. I'd leave off any primary key fields and values in Insert queries.

Member #120589

13 Years Ago

THis is probably the issue:

if($rezultati = mysql_query($drugatab) && mysql_num_rows($rezultati)){

Change to:

$rezultati = mysql_query($drugatab);
if(mysql_num_rows($rezultati)){

   // ...
}

Member #120589

13 Years Ago

The code above gives no output if the select query is successful. What were you expecting?

Edited 13 Years Ago by Member #120589

Member #120589

13 Years Ago

I'm not really sure what you're trying to do. You seem to be using a loop further up from the code listed here. You seem to be duplicating data across tables - this is not a good.

If you need to display data from a number of tables, you should use a JOIN or subqueries. So, if I've understood the issue, you need to restructure your tables to take out this duplication.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Member #921280 · Answer 1 · 2012-12-26T12:52:11+00:00

Hi I dont know
When I use:

$drugatab = "SELECT id_product FROM ps_product_supplier WHERE id_product = '$trenutnired'";

The I get same error but id_product is in both tables?

Member #921280 · Answer 2 · 2012-12-27T10:46:11+00:00

Hi
Now I get a blank screen

        $drugatab = "SELECT id_product FROM ps_product_supplier WHERE id_product = '$trenutnired'";
                $rezultati = mysql_query($drugatab);
                    if(mysql_num_rows($rezultati)){
                // Successful query...
                //mysql_query("INSERT INTO ps_product_supplier (id_product_supplier, id_product, id_product_attribute, id_supplier, product_supplier_reference, product_supplier_price_te, id_currency)  VALUES ('', '$trenutnired', '$trenutnired1',  '$trenutnired2', '$trenutnired4', '$trenutnired5', '$trenutnired6')");
                $countUpdated++;
            } else {
                echo "ID $trenutnired ne postoji u drugoj tablici i trenutno: $countUpdated<br />";
            }
            }

Member #921280 · Answer 3 · 2012-12-27T11:17:26+00:00

I get that all IDs in first tabel do not exist in second but only few not exsist in second tabel?
How can I get correct listing and insert missing values?

USPJESNO ID 1 ne postoji u drugoj tablici i trenutno: 0
USPJESNO ID 2 ne postoji u drugoj tablici i trenutno: 1
USPJESNO ID 3 ne postoji u drugoj tablici i trenutno: 2
USPJESNO ID 4 ne postoji u drugoj tablici i trenutno: 3
USPJESNO ID 5 ne postoji u drugoj tablici i trenutno: 4
USPJESNO ID 6 ne postoji u drugoj tablici i trenutno: 5
USPJESNO ID 7 ne postoji u drugoj tablici i trenutno: 6
USPJESNO ID 8 ne postoji u drugoj tablici i trenutno: 7
USPJESNO ID 9 ne postoji u drugoj tablici i trenutno: 8

        $drugatab = "SELECT id_product FROM ps_product_supplier WHERE id_product = '$trenutnired'";
                $rezultati = mysql_query($drugatab) or die(mysql_error());
                    if(mysql_num_rows($rezultati)){
                // Successful query...

                echo "USPJESNO ID $trenutnired ne postoji u drugoj tablici i trenutno: $countUpdated<br />";
                //mysql_query("INSERT INTO ps_product_supplier (id_product_supplier, id_product, id_product_attribute, id_supplier, product_supplier_reference, product_supplier_price_te, id_currency)  VALUES ('', '$trenutnired', '$trenutnired1',  '$trenutnired2', '$trenutnired4', '$trenutnired5', '$trenutnired6')");
                $countUpdated++;
            } else {
                echo "ID $trenutnired ne postoji u drugoj tablici i trenutno: $countUpdated<br />";
            }
            }

Member #921280 · Answer 4 · 2012-12-27T12:43:51+00:00

No, in the loop I am trying to add values that are missing in second table
in table ps_psroduct supplier some values are missing and I want to add the from ps_product. So I want to list values that are missing and insert them in ps_product_supplier, there is no duplication.
Some values from ps_product are same in ps_product_supplier but not all so I want to filter thoese values and insert them correctly
How to do that?

Member #921280 · Answer 5 · 2012-12-27T18:51:55+00:00

Member #921280

13 Years Ago

How am I duplicating data?

Member #120589 · Answer 6 · 2012-12-27T22:56:04+00:00

Some values from ps_product are same in ps_product_supplier but not all so I want to filter thoese values and insert them correctly

That seems to be duplicating data. Isn't it?

Member #921280 · Answer 7 · 2012-12-28T09:17:35+00:00

yes if you look it as that, but that is the way this database works, so it is not :D

Do you maybe how to make this work?

Member #120589 · Answer 8 · 2012-12-28T12:31:54+00:00

Do you maybe how to make this work?

I'm afraid it's beyond me. I don't see how the data in the two tables relate to each other. Good luck though.

Member #921280 · Answer 9 · 2012-12-28T13:18:41+00:00

Member #921280

13 Years Ago

connection is id_product in this case

PHP MySQL update database

Dani AI

Recommended Answers Collapse Answers

All 13 Replies

Recommended Answers