Barand
-
Posts
24,324 -
Joined
-
Last visited
-
Days Won
794
Everything posted by Barand
-
Mysql join query taking long time to exceute the output
Barand replied to Senthilkumar's topic in MySQL Help
And the other tables?
-
Mysql join query taking long time to exceute the output
Barand replied to Senthilkumar's topic in MySQL Help
Easiest is with mysql workbench. Expand the table and columns Right-click coumn name and select "Create index" Click "Create"
-
That is nothing like the solution I suggested. In fact you ignored just about everything in my posts. If two people run your code simultaneously they will both think it is safe to add the same number and you are back where you started.
-
Mysql join query taking long time to exceute the output
Barand replied to Senthilkumar's topic in MySQL Help
Put indexes on the columns that you are joining on. This speeds things up considerably as it doesn't have to read the entire joined table to find a match. As you can see... The fields you you are joining on should be of the same type. You are joining on columns defined as int in one table and varchar in the other.
-
Forget my last reply then. That relied on there being a single connection. You are going to have to do it the hard way in your code rather than letting the mysql handle it. Only try the other 2 inserts if the first does not fails with a duplicate error.
-
If they are 3 databases on the same server all with the same usernames and passwords then you only need a single connection. A connection is to a server and not to to a database. Try this, so that if any of the inserts fail the other two are cancelled. // make connection to DB server // call mysql_report so that all errors are reported as execptions // this saves you from having to check every mysql function call to see if it worked or not mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $conn = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS); // NOTE - single connection $conn if ($_SERVER['REQUEST_METHOD']=='POST') { $link = trim($_POST['link']); if ($link != '') { try { $conn->begin_transaction(); $stmt1 = $conn->prepare("INSERT INTO DB1.nametable (link) VALUES (?)"); // define which DB to use - DB1, DB2 or DB3 $stmt2 = $conn->prepare("INSERT INTO DB2.nametable (link) VALUES (?)"); $stmt3 = $conn->prepare("INSERT INTO DB2.nametable (link) VALUES (?)"); $stmt1->bind_param('i', $link); $stm1t->execute(); $stmt2->bind_param('i', $link); $stmt2->execute(); $stmt3->bind_param('i', $link); $stmt3->execute(); $conn->commit(); } catch(mysqli_sql_exception $e) { $conn->rollback(); if ($e->getCode() == 1062) { echo "Sorry, this was already sent"; } else { throw $e; } } } }
-
The code you post doesn't insert anything, anywhere. All is does is define the content of the string $sql. Are all three databases on the same server (host) or are you connecting to three separate hosts?
-
You need to implement the method I gave you in PHP. For instance, executing that sql query would be a good move.
-
Your db connection code needs adjustment //make connection to DB server // call mysql_report so that all errors are reported as execptions // this saves you from having to check every mysql function call to see if it worked or not mysqli_report(MYSQLI_REPORT_ERROR|MYSQLI_REPORT_STRICT); $connS = mysqli_connect($servernameS, $usernameS, $passwordS, $databaseS); Now you can remove your code which checks for errors and replace your insert query with the coed I gave you
-
Use try .. catch, for example // pseudocode - in case you didn't notice)... try { insert the record // attemp the insert } catch (exception) { if (exception error code is 1062) { // error detected - was it a duplicate? output your duplicate record message // yes it was so report it } else { throw (exception) // no it wasn't so let php handle the exception } }
-
Something like... <!DOCTYPE html> <html lang="en"> <head> <title>Example</title> <meta charset="utf-8"> <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script> <script type='text/javascript'> $(function() { $(".form-button").click(function(e) { e.preventDefault() // stop button from submitting the form alert("Button " + $(this).val() + " clicked") }) }) </script> <style type='text/css'> #myform { padding: 30px; width: 330px; margin: 20px auto; border: 1px solid blue; text-align: center; } </style> </head> <body> <form id='myform' > <button class='form-button' name='form-button' value='1'>Button 1</button> <button class='form-button' name='form-button' value='2'>Button 2</button> <button class='form-button' name='form-button' value='3'>Button 3</button> <br><br> <button>Submit</button> </form> </body> </html>
-
The relevant link was in my reply... which linked to ... https://www.php.net/mysqli_query
-
Following the link to the required page in the manual that I provided would be a start.
-
RTFM mysqli_query() requires 2 arguments. There is more to switching from mysql_xxx() to mysqli_xxx() than just adding an "i". They are completely different animals.
-
Take look at simpleXML.
-
Is this the effect you are looking for? <head> <style type='text/css'> .curved-bottom { clip-path: ellipse(100% 60% at 50% 40%); background-color: #FFFF00; color: black; } .w3-row { background-color: black; color: white; } .w3-col { padding: 50px 0; text-align: center; } </style> </head> <body> <div class='w3-row'> <div class='w3-col m12 curved-bottom'> I have a curvy bottom </div> </div> <div class='w3-row'> <div class='w3-col m12'> I'm a straight guy </div> </div> </body>
-
The only MyIsam-only functionality that I can think of is the ability to have a compound primary key EG PRIMARY KEY (year, number) where the 2nd part auto_increments within the first part, so if you have CREATE TABLE `test1` ( `year` int(11) NOT NULL, `number` int(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (`year`,`number`) ) ENGINE=MyISAM ; mysql> select * from test1; +------+--------+ | year | number | +------+--------+ | 2022 | 1 | | 2022 | 2 | +------+--------+ mysql> insert into test1 (year) values (2022), (2022), (2023), (2023), (2024); mysql> select * from test1; +------+--------+ | year | number | +------+--------+ | 2022 | 1 | | 2022 | 2 | | 2022 | 3 | | 2022 | 4 | | 2023 | 1 | | 2023 | 2 | | 2024 | 1 | +------+--------+
-
There is definitely an echo in here. @Moorcam WRONG! What about UPDATE and DELETE queries?
-
Write it as a CLI script and use a CRON job to run it as frequently as required
-
Then try running it when it isn't December or before 6am.
-
INSERT queries do not have a WHERE clause. You need to to use an "INSERT ... ON DUPLICATE KEY UPDATE ... " EG INSERT INTO table (id, col_a, col_b) VALUES (?, ?, ?) ON DUPLCATE KEY UPDATE col_a = VALUES(col_a), col_b = VALUES(col_b) ;
-
If this is your insert query... INSERT INTO `Customer` (`SyncroID`, `PrimaryContactID`, `CompanyName`, `Address`, `City`, `State`, `Postcode`, `Country`, `AccountStatus`, `UserName`, `Password_Hash`, `Password_Salt`, `Notes`, `BillingContactID`) VALUES ('12321', NULL, 'test', NULL, NULL, 'QLD', NULL, 'Australia', 4, NULL, NULL, NULL, NULL, NULL); ...there is only a 50% correlation between those column names and the data keys in the customer array... -------------------------------------+----------------------------------------- | Matched | Unmatched | -------------------------------------+----------------------------------------- $customer["id"]; $customer["firstname"]; $customer["lastname"]; $customer["business_name"]; $customer["email"]; $customer["address"]; $customer["phone"]; $customer["city"]; $customer["mobile"]; $customer["state"]; $customer["business_and_full_name"]; $customer["zip"]; $customer["business_then_name"]; $customer["fullname"];
-
Firstly, these lines of code do absolutely nothing... $customer["id"]; $customer["firstname"]; $customer["lastname"]; $customer["fullname"]; $customer["business_name"]; $customer["email"]; $customer["phone"]; $customer["mobile"]; $customer["address"]; $customer["city"]; $customer["state"]; $customer["zip"]; $customer["business_and_full_name"]; $customer["business_then_name"]; Secondly, you are using the least efficient means of inserting data that there is. Single inserts are slow. Marginally faster are prepared statements where you prepare once then execute in a loop with new value for each call. By far the fastest way is use multiple inserts. EG INSERT INTO table (id, name) VALUES (1, 'Name A'), (2, 'Name B'), (3, 'Name 3'); To implement... $data = []; foreach ($customers as $c) { $data[] = sprintf("( %d, '%s')", $c['id'], $c['business_name']); } $pdo->exec("INSERT INTO Customer (SyncroID, CompanyName) VALUES " . join(',', $data));
-
Are you sure you don't revisit index.php between runs of page.php? Might be safer if you only set the value if is not already set if (!isset($_SESSION['abc']) { $_SESSION['abc'] = rand(10000, 99999); }
-
