kjetterman

Upon pressing the submit button for my form, I have two goals 1. If it is a record that already exists in my database, then update said record. 2.. If the record does not exist in the database, create it. I added a column called u_id and assigned it as a unique key. Or at least I thought I did. After running the query you posted above, I got the following results: Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. Here is my current table structure (if it helps): +-------------+-------------+------+-----+-----------+-------------------+ Field | Type | Null | Key | Default | Extra +-------------+-------------+------+-----+-----------+-------------------+ contact_id | int(1) | NO | PRI | NULL | auto_increment u_id | char(32) | NO | UNI | NULL | title | varchar(80) | NO | | NULL | first_name | varchar(100)| NO | | NULL | last_name | varchar(100)| NO | | NULL | job_title | varchar(255)| NO | | NULL | address_1 | varchar(255)| NO | | NULL | address_2 | varchar(255)| NO | | NULL | org_city | varchar(100)| NO | | NULL | org_state | varchar(100)| NO | | NULL | zip_code | varchar( | NO | | NULL | country | varchar(100)| NO | | NULL | phone_number | varchar(15) | NO | | NULL | email_address | varchar(100)| NO | | NULL | org | varchar(150)| NO | | NULL | appquestion | tinyint(1) | NO | | NULL | banner | tinyint(1) | NO | | NULL | bulletin | tinyint(1) | NO | | NULL | giveaway | tinyint(1) | NO | | NULL | app | tinyint(1) | NO | | NULL | tshirt | tinyint(1) | NO | | NULL | promised_tee | tinyint(1) | NO | | NULL | print | tinyint(1) | NO | | NULL | party | tinyint(1) | NO | | NULL | org_notes | varchar(255)| NO | | NULL | notes | varchar(255)| NO | | NULL | +-------------+-------------+------+-----+-----------+-------------------+ I do not get an error message. Basically, anytime a current record is pulled up and change and then submitted via the form, it adds a new record instead of updating the record that the information was pulled from. It would seem then that perhaps I don't understand the process of making a column Unique and how it translates to the two goals listed above. What should I have done?

Okay, so I took all of the records in the database and gave each of them a unique identifier. My new "Unique" key is named u_id. I modified my query a bit but the ON DUPLICATE KEY clause does not appear to be working still. Any thoughts? if($_POST['submit']){ $con=mysqli_connect("localhost","username","password","database_name"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $org = mysql_real_escape_string($_POST['organization']); $namefirst = mysql_real_escape_string($_POST['firstName']); $namelast = mysql_real_escape_string($_POST['lastName']); $emailaddy = mysql_real_escape_string($_POST['email']); $phonenum = mysql_real_escape_string($_POST['phone']); $appquestion = mysql_real_escape_string($_POST['appquestion']); $banner = mysql_real_escape_string($_POST['banner']); $bulletin = mysql_real_escape_string($_POST['bulletin']); $giveaway = mysql_real_escape_string($_POST['giveaway']); $app = mysql_real_escape_string($_POST['app']); $tshirt = mysql_real_escape_string($_POST['tshirt']); $tshirtp = mysql_real_escape_string($_POST['tshirtp']); $print = mysql_real_escape_string($_POST['print']); $party = mysql_real_escape_string($_POST['party']); $orgnotes = mysql_real_escape_string($_POST['notes']); $sql="INSERT INTO database_name (contact_id, u_id, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes) VALUES ('', '','$namefirst','$namelast','$emailaddy','$phonenum','$org','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes') ON DUPLICATE KEY UPDATE first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo "1 record added"; mysqli_close($con); }

Thank you for your answer! The problem is that some of the contacts in the database share the same email address because they come from the same organization. Some of the first and last names are the same too...

I am trying to get this query correct. I want to insert a record into the database upon form submission but only if the record does not already exist. If the record exists, then I want it to be updated in the database. What is happening: Upon form submit, a new record is entered into the database every time. Note: The contact_id column is both primary key and unique in my database. Here is my code: if($_POST['submit']){ $con=mysqli_connect("localhost","username","password","database_name"); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } $org = mysql_real_escape_string($_POST['organization']); $namefirst = mysql_real_escape_string($_POST['firstName']); $namelast = mysql_real_escape_string($_POST['lastName']); $emailaddy = mysql_real_escape_string($_POST['email']); $phonenum = mysql_real_escape_string($_POST['phone']); $appquestion = mysql_real_escape_string($_POST['appquestion']); $banner = mysql_real_escape_string($_POST['banner']); $bulletin = mysql_real_escape_string($_POST['bulletin']); $giveaway = mysql_real_escape_string($_POST['giveaway']); $app = mysql_real_escape_string($_POST['app']); $tshirt = mysql_real_escape_string($_POST['tshirt']); $tshirtp = mysql_real_escape_string($_POST['tshirtp']); $print = mysql_real_escape_string($_POST['print']); $party = mysql_real_escape_string($_POST['party']); $orgnotes = mysql_real_escape_string($_POST['notes']); $sql="INSERT INTO database_name (contact_id, first_name, last_name, email_address, phone_number, org, appquestion, banner, bulletin, giveaway, app, tshirt, promised_tee, print, party, org_notes) VALUES ('','$namefirst','$namelast','$emailaddy','$phonenum','$churchorg','$appquestion','$banner','$bulletin','$giveaway','$app','$tshirt','$tshirtp','$print','$party','$orgnotes') ON DUPLICATE KEY UPDATE first_name = '$namefirst', last_name = '$namelast', email_address = '$emailaddy', phone_number = '$phonenum', org = '$org', appquestion = '$appquestion', banner = '$banner', bulletin = '$bulletin', giveaway = '$giveaway', app = '$app', tshirt = '$tshirt', promised_tee = '$tshirtp', print = '$print', party = '$party', org_notes = '$orgnotes'" ; if (!mysqli_query($con,$sql)) { die('Error: ' . mysqli_error($con)); } echo "1 record added"; mysqli_close($con); } From everything I have read, I need to use ON DUPLICATE KEY UPDATE to replace the old information with new information in the database upon form submission. While the insert part of my code is working, the portion with ON DUPLICATE KEY UPDATE is not working. Why might this portion of the code not be working? Is there a better way to insert else update the information? Thank you for any help or guidance you can give me! I've been working on this concept for three days and have read a ton of information about it, but am still not able to get it to work.

When I click on the letter of the alphabet on my form page, it will bring up the results of my query. I got this part working. The next part is to be able to click on any of the populated results and have it autofill my form with organization, first name, last name, email address and phone number. I know that I need to use Ajax and jQuery to accomplish this. Here is my html <!-- Letter Search --> <div class="searchBox span12"> <h3>Choose the First Letter of the Person's Last Name</h3> <ul> <li class="alphabets" id="A"><a href="?by=A">A</a></li> <li class="alphabets" id="B"><a href="?by=B">B</a></li> <li class="alphabets" id="C"><a href="?by=C">C</a></li> <li class="alphabets" id="D"><a href="?by=D">D</a></li> <li class="alphabets" id="E"><a href="?by=E">E</a></li> <li class="alphabets" id="F"><a href="?by=F">F</a></li> <li class="alphabets" id="G"><a href="?by=G">G</a></li> <li class="alphabets" id="H"><a href="?by=H">H</a></li> <li class="alphabets" id="I"><a href="?by=I">I</a></li> <li class="alphabets" id="J"><a href="?by=J">J</a></li> <li class="alphabets" id="K"><a href="?by=K">K</a></li> <li class="alphabets" id="L"><a href="?by=L">L</a></li> <li class="alphabets" id="M"><a href="?by=M">M</a></li> <li class="alphabets" id="N"><a href="?by=N">N</a></li> <li class="alphabets" id="O"><a href="?by=O">O</a></li> <li class="alphabets" id="P"><a href="?by=P">P</a></li> <li class="alphabets" id="Q"><a href="?by=Q">Q</a></li> <li class="alphabets" id="R"><a href="?by=R">R</a></li> <li class="alphabets" id="S"><a href="?by=S">S</a></li> <li class="alphabets" id="T"><a href="?by=T">T</a></li> <li class="alphabets" id="U"><a href="?by=U">U</a></li> <li class="alphabets" id="V"><a href="?by=V">V</a></li> <li class="alphabets" id="W"><a href="?by=W">W</a></li> <li class="alphabets" id="X"><a href="?by=X">X</a></li> <li class="alphabets" id="Y"><a href="?by=Y">Y</a></li> <li class="alphabets" id="Z"><a href="?by=Z">Z</a></li> </ul> <? include('search.php'); ?> </div> <hr style="color:#ccc; margin-bottom:20px;" /> <!-- Main Form --> <div id="mainForm"> <form method="post" id="icsForm" class="searchBox span12"> <div id="col1" class"span6"> <h3>Contact Information</h3> <label>Church / Organization:</label><input type="text" name="organization" id="organization" class="span6 upright" /><br /> <label>First Name:</label><input type="text" name="firstName" id="firstName" class="span6 upright" /> <label>Last Name:</label><input type="text" name="lastName" id="lastName" class="span6 left upright" /> <label>Email Address:</label><input type="text" name="email" id="email" class="span6 left upright" /> <label>Phone Number:</label><input type="text" name="phone" id="phone" class="span6 left upright" /> </div> </div> Here is my php if(preg_match("/^[A-Z | a-z]+/", $_POST['name'])){ $name=$_POST['name']; } if(isset($_GET['by'])){ $letter=$_GET['by']; //query to sort by last name $sql="SELECT contact_id, first_name, last_name, church_org, email_address, phone_number FROM ics_data WHERE last_name LIKE '$letter%' ORDER BY last_name ASC"; //run the query against the mysql query function $result=mysql_query($sql); //count results $numrows=mysql_num_rows($result); echo "<p>" .$numrows . " results found for " . $letter . "</p>"; //Create while loop and loop through result set while($row=mysql_fetch_array($result)){ $first_name=$row['first_name']; $last_name=$row['last_name']; $church_org=$row['church_org']; $email_address=$row['email_address']; $phone_number=$row['phone_number']; $contact_id=$row['contact_id']; //display the result of the array echo "<div id=\"search-results\">"; echo "<ul class=\"letter-results\">\n"; echo "<li class=\"result-row\">" . "<a href=\"#\" class=\"testclass\">" .$first_name . " " .$last_name . "". ", " ."" .$church_org ."</a></li>\n"; echo "</ul>"; echo "</div>"; } } Here is my Javascript file (Ajax) $(document).ready( function() { function formfill() { var organization = $('#organization').val(); var firstname = $('#firstname').val(); var lastname = $('#lastname').val(); var email = $('#email').val(); var phone = $('#phone').val(); $.ajax ({ method: "GET", url: "search.php", dataType: 'json', data: { organization:organization, firstname:firstname, lastname:lastname, email:email, phone:phone }, type: "POST", success: function(data) { $organization $firstname $lastname $email $phone }, failure: function() { alert('fail!'); } }); } I know that I do not have a reference yet to JSON in my php file and that it is needed. I'm not solid on the Ajax part. That is the part that is tripping me up. I know that I need to make the form autofill when clicking on a specific result returned from my query... but i'm not sure how to do that. Thank you in advance for any help or advice you can give!! I am relatively new to programming. Hopefully I posted this in the right forum as a lot of these technologies overlap.

Thank you for your help with this!

Oh my goodness!! Such a simple solution! So, if I am understanding this correctly, then the leading % was causing the query to search the entire string for that letter, instead of just picking out the first letter..... right?

Okay so, I had to completely re-work the way that I was thinking about grabbing the information from the database. I got rid of the JSON for now. Here is my new code: if(isset($_GET['by'])){ $letter=$_GET['by']; //My Query $sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name REGEXP '^[a-zA-Z]'" . $letter . '^[a-zA-Z]'; What I am trying to do is have the results generated by the first letter of the last name. If I query like this: $sql="SELECT contact_id, first_name, last_name, church_org FROM ics_data WHERE last_name LIKE '%" . $letter . "%'"; It works, but will pick out the letter I click on and find said letter in any place within the last name. So for example, I want the query to be able to correctly find the person with the last name Smith, when I click on the letter "S". I do not want a person with the last name of Karns to be included when I click on the letter "S" simply because "S" is in the name somewhere. If that makes sense? I've read tons of documentation and have tried to get it to work but sadly cannot figure it out. Any advice or direction would be much appreciated!! TIA

Hi! I'm fairly new to PHP & MySQL (from a programmer's perspective) and have just started seriously building my own applications using PHP. I need some help bridging the gap with this MySQL query I have created that is to find only the organizations that have names that start with the letter 'A'. The initial query that I have written does work. However, I need to refine the query to "skip" over the first word -- example "The" so that I can get the complete correct results. Here is my code: include_once("dbconnect.php"); $linkA = "SELECT church_org FROM ics_data WHERE church_org LIKE 'A%' ORDER BY church_org ASC"; $queryA = mysql_query($linkA); $dataA = array(); while ($row = mysql_fetch_array($queryA) ) array_push ($dataA, array('church_org' => $row[0])); echo json_encode(array("dataA" => $dataA)); This fetches every organization that starts with the letter 'A'. However, some organizations have St. or The in front of their names. So the query doesn't pick those up because it doesn't match what I have specified. I have tried the following: $linkA = "SELECT church_org FROM ics_data WHERE church_org LIKE 'A%' & TRIM('St. ', 'St ' church_org) ORDER BY church_org ASC"; This doesn't do what I wanted it to do and after further research, I believe it is because TRIM only trims the words specified from the end-result string. I *think* I need to use REGEX to define a better pattern for the query but am still a little confused after reading the documentation on how to go about building that out. How would I accomplish this? I appreciate any help or guidance! Hopefully, I have posted this in the right forum.

Does it make a difference if I want the totals to auto-populate upon user-input without hitting a "calculate" or "submit" button?

I need to make part of my form auto-populate calculated values based on user input. Here is what I have so far: The HTML <div class="PaymentDetails"> <fieldset> <legend>Payment Details</legend> Ad Charge $ <input type="number" name="AdPrice" id="AdPrice" class="medium" value="" /><br /> Color Charge + <input type="number" name="ColorCharge" id="ColorCharge" class="medium" value="" /><br /> Web Ad + <input type="number" name="WebAd" id="WebAd" class="medium" value="" /><br /> Subtotal = <input type="number" name="AdSubtotal" id="AdSubtotal" class="medium" value=<? $SubTotal ?> /><br /> Down Payment - <input type="number" name="DownPayment" id="DownPayment" class="medium" value="" /><br /> Total = <input type="number" name="TotalPrice" id="TotalPrice" class="medium" value=<? $TotalPrice ?> /><br /><br /> # Consec. Payments \ <input type="number" name="Payments" id="Payments" class="medium" value="" /><br /> Amt Each Payment $ <input type="number" name="Payment" id="Payment" class="medium" value=<? $Payment ?> /><br /><br /> <input type="checkbox" name="ProratedCheck" id="ProratedCheck" /><label>Prorated/Length</label><input type="text" name="ProratedLength" id="ProratedLength" /> </fieldset> </div> The PHP <? $AdPrice = $_POST['AdPrice']; $ColorCharge = $_POST['ColorCharge']; $WebAd = $_POST['WebAd'] + $Number; $SubTotal = $AdPrice + $ColorCharge + $WebAd; $DownPayment = $_POST['DownPayment']; $TotalPrice = $SubTotal - $DownPayment; $Payments = $_POST['Payments']; $Payment = $TotalPrice / $Payments; ?> I know that i'm probably missing a fairly big concept here... for instance: How does the code know that a number has been entered and that a number needs to be output? Warning: Division by zero <--- i'm getting this error message also... Thank you for any help or guidance you can give!

I've been able to do this successfully using Javascript but not PHP.

Congrats!!

Found the answer! In the original script find this: is('win') ? 'win' + (is('windows nt 6.0') ? ' vista' : '') : replace with: is('win') ? 'win' + (is('windows nt 6.0') ? ' vista' : is('windows nt 5.1') || is('windows nt 5.2') ? ' xp' : '') :

this is the line I added to get xp to show up: is('win') ? 'win' + (is('windows nt 5.1') || ('windows nt 5.2') ? ' xp' : '') : It didn't work though. No syntax error -- so obviously a logical error.