Jump to content

Search the Community

Showing results for tags 'mysql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Calendars

  • Community Calendar

Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 249 results

  1. PHP Form with MySQL queries

    <?php include "connetti.php"; session_start(); $query = "SELECT id_studente, nome, cognome, anno_maturita, voto_maturita FROM utenti_studenti WHERE utenti_studenti.confermato = 0"; $risultato = @mysql_query($query); if (mysql_num_rows($risultato) == 0) { echo("Nessun elemento trovato"); header("refresh:3;url=AreaAmministratore.phtml"); exit(); } ?> <html> <head> <title>Annuario Studenti</title> <link rel="stylesheet" href="cssUtils/aggiungi_properties.css"/> <script type="text/javascript" src="jsUtils/jsUtils_annuario/annuario_properties.js"></script> </head> <body> <form method='post' action='confermaStudenti.php' name='aggiornaStatoStudente'> <div align='center'> <table class='tg'> <tr> <th class='tg' style='color: #000;'>ID</th> <th class='tg' style='color: black'>Nome</th> <th class='tg' style='color: black'>Cognome</th> <th class='tg' style='color: black'>Anno Maturita</th> <th class='tg' style='color: black'>Voto Maturita</th> <th class='tg' style='color: black'>Accetta</th> </tr><?php while ($row = @mysql_fetch_assoc($risultato)) { echo " <tr align='center'> <td class='tg' style='color: black'>" . $row['id_studente'] . "</td> <td class='tg' style='color: black'>" . $row['nome'] . "</td> <td class='tg' style='color: black'>" . $row['cognome'] . "</td> <td class='tg' style='color: black'>" . $row['anno_maturita'] . "</td> <td class='tg' style='color: black'>" . $row['voto_maturita'] . "</td> <td class='tg' style='color: black'>" . "<input type=\"hidden\" name=\"rifiuta\"/><input type=\"checkbox\" name=\"accetta\"/>" . "</td> </tr>"; } ?> </table> </div> <div align='center'> <a href='AreaAmministratore.phtml'>Torna indietro.</a> <input type='submit' value='Prosegui'/> </div> </form> </html> <?php session_start(); include("connetti.php"); if (isset($_POST["accetta"])) { $accetta = 1; } else { $accetta = 0; } if ($accetta) { $cognome = $_POST['cognome']; $nome = $_POST['nome']; $id_studente = $_POST['id_studente']; $query = "UPDATE utenti_studenti SET confermato='1' WHERE nome='$nome' AND cognome='$cognome' AND id_studente='$id_studente'"; $risultato = @mysql_query($query) or die('<p align="center">Errore!</p>' . mysql_error()); echo("<script>alert('La modifica eseguita')</script>"); header("refresh:0;url='AreaAmministratore.phtml'"); } else { echo "<script>alert('Errore');</script>"; header("refresh:0;url='AreaAmministratore.phtml'"); exit(); } ?> Greetings guys, could you help me with those codes above? Cuz I can't understand why it doesn't work... So I have an table with the users that not confirmed. So to confirm them I've made checkbox, so on the other side I have a control if the checkbox is checked so I need to update some values on my database right ? Well it doesn't work....
  2. Hello Every One I Want Show all USer last Useges Data From date of Last Recharge to To Date and condition With user owner by i Have 3 Table invoice - Need data from column date(need last invoice Last Recharge date For Below Table Condition Start ),username( Uniq In all table) acct - > Need data from column acctstarttime( For Start Date ),SUM( acct.acctinputoctets + acct.acctoutputoctets ) AS data(For Sum Of Useges Data ),SUM( acct.acctsessiontime ) AS acctsessiontime,username(Uniq In all table) users - > Need data from column username( Main Table Match username on this table base),owner,lastlogoff,expiration,uptimelimit,comobolimit, My Code is Here But Not working SELECT DATE( `acctstarttime` ) AS acctstarttime, SUM( acct.acctinputoctets + acct.acctoutputoctets ) AS data, acct.username, SUM( acct.acctsessiontime ) AS acctsessiontime, users.username, users.owner, users.lastlogoff, users.expiration, users.uptimelimit, .comblimit, users.enableuser,invoices.date FROM invoices,acct JOIN users ON acct.username = rm_users.username WHERE users.owner = 'admin' and acctstarttime between 'invoice.date' and 'users.expiration' GROUP BY MONTH( `acctstarttime` ) , acct.username ORDER BY invoices.date DESC LIMIT 0 , 30 I Think Problem in Where condition i want acctstarttime between 'invoice.date(Desc or User last recharge Date)' and 'users.expiration' I m very Confused How I dow Any One Can Help Thanks in Extra
  3. financial year calculation in php

    Hello everyone I want Show Some Recorded according financial year For Example from 01-04-2016 to 31-3-2017 01-04-2017 to 31-03-2018 my Code $pst = date('Y'); $pt = date('Y', strtotime('+1 year')); $sql="SELECT *FROM mytable where date BETWEEN CAST('$pst-04-01' AS DATE) AND CAST('$pt-03-31' AS DATE)"; Anyone Can explain how i done this
  4. Group by value ( remarks ) below tr

    Below is products table : id | mid | wgh | remark| remkok | 1 3 1.5 r3ok 1 2 2 1.5 0 3 2 0.6 nice 0 4 1 1.2 okh 0 5 4 1.5 bye 0 6 4 2.4 okby 0 7 3 3.0 oknice 1 I want to display remark below tr of group by mid ....like below mid wgh 3 1.5 3.0 remarks : r3ok, oknice 4 1.5 2.4 remarks : bye, okby 2 1.5 0.6 remarks : , nice 1 1.2 remarks : okh **What i have tried as below :** $pid= null; while($row = mysql_fetch_array($result)) { $rowpkts = $row['mid']; echo "<tr class=\"undercl\">"; if($rowpkts != $pid){ echo'<td align="center" valign="top">'.$row["mid"].'</td>'; }else{ echo'<td align="center" valign="top"></td>'; } echo'<td align="center" valign="top">'.$row["wgh"].'</td>'; echo "</tr>"; // what i tried to build for remarks as below $remsql = "SELECT mid as onu , GROUP_CONCAT(`remark` ORDER BY `id` ASC SEPARATOR ', ') AS plrmks FROM products WHERE remkok= 1 GROUP BY `mid`"; $fetchremk = mysql_query($remsql); $rowresults = mysql_fetch_array($fetchremk); if($rowresults['onu'] == $pid ){ echo"<tr style='border-style:underline;'>"; echo'<td align="center" align="top">'.$rowresults["plrmks"].'</td>'; echo"</tr>"; } } $pid = $rowpkts; } But remarks is not coming proper ...i means its not display below mid=3 or mid=1.....
  5. Showing data that no longer exist

    Hello everyone, I am not sure if this is the right place to post since I do not know for sure if it is PHP that causes my issue, so I apologize in advance if I shouldn't have posted here. I am using WampServer Version 3.0.0 64bit (Apache: 2.4.17 | PHP: 5.6.16 | MySQL: 5.7.9) on windows10. I am updating a website and I am keeping the database the same, except from some small optimizations here and there. I have a query that selects all active rows of a specific category, then I fetch the results and show the rows: if(mysqli_stmt_prepare($stmt, "SELECT * FROM myTableName WHERE _category=? AND _approved='1' ORDER BY _id DESC LIMIT ?, ?")) { mysqli_stmt_bind_param($stmt, "iii", $category, $offset, $rowsPerPage); mysqli_stmt_execute($stmt); mysqli_stmt_bind_result($stmt,$id,$title,...,$image,...); //all fields are binded, including image while(mysqli_stmt_fetch($stmt)) { //show recipes } } The query is correct and everything shows up fine, except from the image returned by the query. My old database used to store the image with the path, eg "/this/is/the/path/to/image.jpg" but I have updated the image table column to only store the file name so now $image variable should contain only the file name, eg "image.jpg". But it does not. The $image variable still contains the full path, although the table is updated! I though it was some kind of caching, but clearing the browser's cache, going incognito, hard refreshing, changing browser did not change anything. I also tried running: SET GLOBAL query_cache_size = 0; in mySql terminal but that did not change anything as well. I also tested php.ini for Op Cache but it was already disabled: opcache.enable=0 I do not know what to do about this and it is driving me crazy! Any help will be very much appreciated. Thank you all, georgia
  6. Hello Everyone I M in Problem i Am creat my 1st project for client so plesese help me that how i done i need how i calculate field in php like this Pic total of Credit total of debit and pending balance and grand total So tell Me how i done Below i attecthed
  7. cant move column position PHPMYADMIN

    In PHPmyadmin I can't move the position of the columns. So when I go to change/modify (not sure how it's called in English) I get the possibility to move column and the place it after another column then where its placed now. Though, it does print the query it goes like ALTER TABLE `my_tab` CHANGE `col1` `col1` TINYINT(1) NULL DEFAULT NULL AFTER `col5`; I have not tested in the mysql console, but in PHP the columns don't move (anymore) like they did do beforehand.
  8. Hi everyone! Thank you in advance for reading this and any help you are able to provide. This has been a bit of a long road but I'm learning along the way. Before I begin I am well aware of the dangers of SQL injection and understand that using prepared statements would decrease injection attacks for the following code. This is a PHP/MySQL test code to see if it works before actual implementation on a live site. With that said here we go: I have a database and it contains four tables (for the sake of security I gave them disney character names) named huey, dewey, lewey and uncledonald. I would like to have the values from the columns deweysays in the table dewey, hueysays from the table huey and leweysays from the table lewey to show up in thier corresponding deweysays, hueysays and leweysays columns in the table uncledonald. See attached pic to see visually what I mean. I've tried the following code and get the result I want (values added to all tables) but only once. After that I get data in the dewey, huey and lewey tables but nothing else in the uncledonald table. Here is the PHP: <?php //Let's see whether the form is submitted if (isset ($_POST['submit'])) { $con=mysqli_connect("localhost","root","root","provingground"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $sql = "INSERT INTO dewey (lot_id, deweysays) VALUES (0, '{$_POST['deweyspeak']}');"; $sql .= "INSERT INTO huey (cust_id, hueysays) VALUES (0, '{$_POST['hueyspeak']}');"; $sql .= "INSERT INTO lewey (personal_id, leweysays) VALUES (0, '{$_POST['leweyspeak']}');"; $sql .= "INSERT INTO uncledonald (deweysays) SELECT deweysays FROM dewey "; $sql .= "INSERT INTO uncledonald (hueysays) SELECT hueysays FROM huey "; $sql .= "INSERT INTO uncledonald (leweysays) SELECT leweysays FROM lewey "; // Execute multi query if (mysqli_multi_query($con,$sql)){ print '<p> The Ducks Have Spoken.</p>'; } else { die ('<p>Could not add entry because:<b>' . mysqli_error() . '</b>.</p><p>The query being run was: ' . $sql . '</p>'); } } mysqli_close($con); ?> Is there something missing in my $sql query to uncledonald? Is the script completely off? Lot of questions…Help please!
  9. doing event doesn't working

    http://prntscr.com/a3dz84 please check why it doesn't working ? i have been tested change date in local computer but nothing happen, thanks UPDATE `cu_employee` SET `cu_employee_sisa_cuti` = '12' WHERE 1
  10. Hello everyone, I'm brand new to PHP and MySQL and I'm trying to build a login/register form for my company's website. I've literally scavenged the internet for the past 3 days and have watched multiple tutorials but still can't get my database to link to the php file(s). If anyone can guide me in the right direction or provide a dummy proof tutorial, it would be greatly appreciated!!! (I would attach my php code but I literally have nothing and have gotten no where) Any advice would help. Thanks!
  11. So recently I got my hands on one of youtube videos, it's quit old, and the guy explaining how to create a form for uploading image and all data that is connected with it, to MySQL and then showing on your web site. So everything is ok, but webpage does not display any images, it just show me white picture frame with picture icon on the top. This is my connection file with database: <?php $hostname_phpimage = "***"; $username_phpimage = "***"; $password_phpimage = "***"; $database_phpimage = "***"; // Create connection $inkedmen_marko = mysql_pconnect($hostname_phpimage, $username_phpimage, $password_phpimage); // Check connection if ($phpimage->connect_error) { die("Connection failed: " . $phpimage->connect_error); } ?> Then the file that uploads the images: <?php require_once('php/connect.php'); if($_POST['submit']) { $name=basename($_FILES['file_upload']['name']); $t_name=$_FILES['file_upload']['tmp_name']; $dir='/home4/inkedmen/public_html/images'; $cat=$_POST['cat']; if(move_uploaded_file($t_name,$dir."/".$name)) { mysql_select_db($database_phpimage, $inkedmen_marko); $qur="insert into anglija (mid, cid, name, path) values ('','$cat','$name','/home4/inkedmen/public_html/images/$name')"; $res=mysql_query($qur,$inkedmen_marko); echo'file upl success'; } else { echo 'not uploaded'; } } ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> </head> <body> <form action="upload.php" method="post" enctype="multipart/form-data"> <input type="file" name="file_upload"/> cat_id <input type="text" name="cat" /><br/> <input type="submit" name="submit" value="upload"/> </form> </body> </html> Then this one shows me what countries I have in one of the data base tables, so there is two england and slovenia, they both have a separate id that I have to type when uploading an image: <?php require_once('php/connect.php'); mysql_select_db($database_phpimage, $inkedmen_marko); $qur="select * from cat"; $res=mysql_query($qur,$inkedmen_marko); ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> </head> <body> <?php while($row= mysql_fetch_array($res)) { ?> <h1><a href="image.php?cid=<?php echo $row['id']?>"><?php echo $row['name'] ?> </a></h1><br/> <?php } ?> </body> </html> And finally, when I push on each country it directs me to the pictures that are connected with that countries according to the id given: <?php require_once('php/connect.php'); mysql_select_db($database_phpimage, $inkedmen_marko); $id=$_GET['cid']; $qur="select * from anglija where cid='$id'"; $res=mysql_query($qur,$inkedmen_marko); ?> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> </head> <body> <?php while ($row=mysql_fetch_array($res)){ ?> <img src="<?php echo $row['path'] ?>" width="300px" height="200px"> <br/> <?php } ?> </body> </html> So in the end it directs me to the pictures, but I can't see the pictures, it's just white windows with icon on the top. The pictures uploads to the image folder, the id, names of the file, path also uploads good, but somehow I can't show the images. I know it should be something small, but I can't figure it out. Maybe someone have any ideas????
  12. First, I've read all that W3 has posted about potential web servers plus followed those links and as many peripheral ones until I found myself totally snowed under by technical references and nomenclature that means nothing to me. I have never run a web server and never plan to publicly. This is only for local use of MySQL and PHP. Perhaps there are some who can help point me in a more productive direction or assess the feasibility of my plan to develop a rather straight-forward and (hopefully) simple-to-implement prototype of web pages on my desktop. Here is where I am: The host service that I am considering (and experimenting with at this writing) is a free site that provides better functionality with an upgrade that seems worthwhile, at least at this point it does. It supports MySQL database & PHP, both which are alien to me but seem relatively easy to manipulate, script-wise. Seems a reasonable undertaking. I DL'd MySQL so that I could learn locally without the planned host's interface further complicating the learning curve. I've now gotten familiar enough to manage MySQL data structure that it is time to manage the input, query and reports with PHP/HTML. I am familiar with HTML for the most part as well as javascript but JS is not supported universally the way it used to be. Hence, the PHP route. I find out that I must actually configure my machine to act as a web server to interface between MySQL and PHP. As stated before, this is the milestone where I am stuck. I have no clue which download is appropriate for my needs or how to determine that. I have a stock version of Win 7 Pro that came installed with my HP 6305. Whatever architecture had been installed during the set-up is what it there, plus any of the updates that have been flying through. So my questions are: Where/how do I check this Win 7 machine to decide which server is suitable for this rig? SPECIFICALLY, what should I be concerned most with a potential server to perform functions that are not exotic... just simple input forms, database queries and reports? Bare bones but reliable is what I am seeking. I already installed MySQL Server 5.7 and spent a few weeks running test operations. Do I need to remove it before I install the server and then reinstall? Is there a preferred sequence to install them? I've found right off that MySQL uses command prompt interface, like deja vu of writing Unix shell scripts 25 years ago. That itself was a surprise to me in this world of GUIs. I noticed a Workbench utility that appears to be GUI but at the moment I am comfortable enough learning the syntax in DOS. But this is very very slow process and I see where simple tasks that rely on perfect keystrokes will become tedious. How much help is a MySQL GUI utility and are there any servers that operate seamlessly through that sort of interface? Does the version of MySQL installed determine which web server will be compatible with it? There are more questions, but these high-level, basic ones were chosen to give me a better overall grasp of the project scope. Any objective recommendations would be gratefully received. Please provide links, if applicable. TIA Chip
  13. find identical input with php or mysql

    Hi I'm looking for php/mysql code to find identical input in a mysql database column.
  14. how to resolve this error

    Arrrh! Can't install database. SQLSTATE[42000]: Syntax error or access violation: 1071 Specified key was too long; max key length is 1000 bytes, query was: CREATE TABLE `email_triggers` ( `id` int(10) NOT NULL AUTO_INCREMENT, `enabled` enum('0','1') COLLATE utf8_unicode_ci NOT NULL, `trigger_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL, `observer` varchar(255) COLLATE utf8_unicode_ci NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `trigger_name_2` (`trigger_name`,`observer`), UNIQUE KEY `trigger_name_3` (`trigger_name`,`observer`), KEY `trigger_name` (`trigger_name`), KEY `observer` (`observer`), KEY `enabled` (`enabled`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  15. Cart Order to History Order table

    Dear all, i have been working in my college project and i reached a point were i can't get my cart orders into Order history, as it's not working and keep giving me the following error page, Error : INSERT INTO `cib4003_h00233671_at`.`orderhistory`(`order_id`, `Product_ID`, `Product_Name`, `client_ID`, `quantity`) VALUES ('', '', '', '2', ''); Duplicate entry '0' for key 'PRIMARY' ^ i checked the databse and all values are 0 except the Client_ID... thats why i am getting Duplicate entry, however, i tried to fix the problem for the past few hours and i can't find the issue, here my cart page code and here my history php where i do the insert part
  16. Database error

    I uploaded website files to the web root and when i tried to access it via www.mydomain.com it gave following error A Database Error OccurredError Number: 1146 Table 'webpk_16901224_app.sesion' doesn't exist INSERT INTO `sesion` (`session_id`, `ip_address`, `user_agent`, `last_activity`, `user_data`) VALUES ('1912dd6c2dd5c411cde798d9496c9a9a', '119.157.163.66', 'Mozilla/5.0 (Windows NT 5.1; rv:42.0) Gecko/20100101 Firefox/42.0', 1448544788, '') Filename: libraries/Session.php Line Number: 328 I tried to create a table like following through phpmyadmin, but i get an error "key id does not exist". My database name is webpk_16901224_app and it is empty. There are no tables. create table tablename( id INT NOT NULL, name VARCHAR (20) NOT NULL, age INT NOT NULL, PRIMARY KEY (id) ); The website script has 3 sql files, but i don't know how to use them. SQL files are attached. Desktop.zip
  17. opencart admin php problem

    hello, Maby you can help me make somthing I have done things myself but i am a litle stuck it has somthing to do with my admin of product list like you can see in the picture i have done alot but now i want to ad a percentage i know that it has somthing to do with taking the result out of the column named (winstverlies) and devide that trough the purchese price and multiply this by 100 but i can not implement this in my code. the code is PHP so who can help me? so i want it to look like on the picture. is this posible? picture is in the file named percentage.jpg So i would be verry pleasd if you could help me?
  18. Collation, Unique vs Index

    I am creating a table using phpMyAdmin. I would appreciate if someone would explain: In the collation, I am selecting utf8_general_ci. Is this OK? For indexing, when should I use ''unique and when would I use 'index'?
  19. Good Practice for Financial Tables

    Hi, I am considering to have two tables related to investments: 1) investors 2)invested money, with a link between the two. For the second table, besides own id and id linking to the first table, there are: investment received, paid money (e.g. profits, paid capital etc.), and dates associated with investment and paid money. There is also some money deductible which is relevant to tax, exchange rate, expenses, etc. I have two options: having those deductions as part of the second table with proper formulas, or keep the table simple and have deductions not in the table but rather as part of the accounting work in accountants' books. I would appreciate if someone knows what could be the best/advisable professional practice.
  20. Capturin Data in Contact us Form

    What is the best way of capturing data sent via a contact-us form (I am using form and PHP)--in DB only, in a dedicated email only, or in both? Pros and cons of each method please, if possible.
  21. 2006 - "MySQL server has gone away" error

    Hi everybody, I am trying to import a db in myphpadmin which works fine on another server and I get the error below. Can you tell me what's wrong here? Thank you. Roughly translated it s: Static analysis: 1 error was found during analysis. Keyword not recognized. (near "ON" at position 25) SQL Query: SET FOREIGN_KEY_CHECKS = ON; MySQL message: #2006 - MySQL server has gone away
  22. I dropped a column (linknum) in a table (wine) with mysql in the console After that I wanted to make it again ALTER TABLE wine ADD linknum INT DEFAULT '1' NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST; but now I recieve an error message like this: ERROR 1067 (42000): Invalid default value for 'linknum' How can I solve it, and create a column called linknum again, starting with 1 and incrementing 1 ?
  23. This is my situation If variable $conn has all variables (with a value) $conn = new mysqli($servername, $username, $password, $dbname); and then you use var_dump($conn); and the output would be null of all the values of $conn. What would it mean? Am I right the connection then needs to be re-established? I'm doing 2 queries on a database. First query is SHOW TABLES then make the user choose a table value. From this value a new query is done. So first connection is done. But can I use this connection or should I make a new one?
  24. I have a question about security and MYSQL. I installed MYSQL and PHPMYADMIN. I changed the root password. But when in PHPMYADMIN I see a list of users and priviliges. Usernames sound like: All, root or pma. I understand I need pma or root, but what is the use of All ? User name Host Password Global privileges User group Grant Action Any % -- USAGE Any localhost No USAGE pma localhost Yes USAGE root 127.0.0.1 Yes ALL PRIVILEGES root ::1 Yes ALL PRIVILEGES root localhost Yes ALL PRIVILEGES Furthermore which priviliges and global rights should a user have when developing localhost?
  25. //Determine who receives bracelets and update bracelet recipients table //This is only applicable to recipients of CHARMS $sql = "SELECT DISTINCT ACCOUNT_NUM FROM sc_master WHERE REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' AND PROCESSED <> 'X' LIMIT 1000"; $qry = mysql_query($sql) or die(mysql_error()); while($r = mysql_fetch_array($qry)) { $acct = $r['ACCOUNT_NUM']; $fname = $r['FNAME']; //no data due to DISTINCT clause $lname = $r['LNAME']; //no data due to DISTINCT clause $bsql = "SELECT ID FROM sc_bracelet_recipients WHERE CONSULTANT_ID = '$acct'"; $bqry = mysql_query($bsql) or die(mysql_query()); $count = mysql_num_rows($bqry); if($count < 1) { //get count of how many bracelet records were marked $bracelets = $bracelets + 1; //update sc_master with an "X" for each bracelet needed mysql_query("UPDATE sc_master SET BRACELET = 'X' WHERE ACCOUNT_NUM = '$acct' AND REFERENCE = '$reference' AND AWARD NOT LIKE 'Scentsational Start Award%' AND GIFT_GROUP = 'Charm' LIMIT 1") or die(mysql_error()); //update sc_bracelet_recipients to track these records mysql_query("INSERT INTO sc_bracelet_recipients (CONSULTANT_ID, FIRST_NAME, LAST_NAME, BATCH_MONTH) VALUES('$acct', '$fname', '$lname', '$reference')"); } //mark the record as processed to avoid reprocessing it on the next pass mysql_query("UPDATE sc_master SET PROCESSED = 'X'") or die(mysql_error()); } I have a script that we run once a month on a large 'awards' list provided by a customer. A recipient can be on the list more than once for different achievements, and for certain awards they may receive a charm for a bracelet. If they are receiving a charm for the first time we must send them a charm bracelet, however if they have already received a bracelet all we send is the charm. Here's a summary of steps: Is the recipient award associated with a charm?Yes - check the 'bracelets' table to see if they have received a bracelet already If they haven't received a bracelet: update the bracelets table indicate that the record receives a bracelet if this recipient receives multiple awards, only mark 1 record for a bracelet [*]Mark all records that have been processed as processed The 'batch' of data that we process is anywhere from 5,000 to 15,000 records per month. The logic noted above runs (loops) for each record in the 'batch' and the bracelet table contains a couple hundred thousand records. This just kills the server... so, you'll see that I limit the primary query to 1,000 records, this is due to how slow this script is (the tables contain hundreds of thousands of records). I'm guessing there's someone far smarter than I that can probably handle all of this in one beautiful and much faster query, preferably fast enough to process the entire list in one run vs. breaking it into groups of 1,000 per run. Thanks in advance for your help! Rumble
×