PHP - Mysql Insert If Not Exists?

Say you have three tables like this...   STUDENTS (studentid, name)   CLASSES (studentid, classname)   GRADES  (studentid, grade)   And not all students have grades yet.  If you're doing a mysql query like below, is there anything you can add to the statement to check if a grade exists for each student?   So that if while lopping through the results of the main query, I can easily do something with the students who don't yet have a grade yet (like maybe mark their names in red).   I can do this by putting a whole separate mysql query inside the looping of the main query, but that seems terribly inefficient to call the database again for EVERY student.

$sql = "SELECT * FROM students, classes, grades
	WHERE students.studentid = classes.studentid
        ORDER BY students.lastname";
	$info= mysqli_query($connection, $sql);
	if (!$info) {
	die("Database query failed: " . mysqli_error());
	} else {
           //code
        }

Thanks! Greg

Hey Everyone, I want to start out by saying thanks in advance for reading this. Since I started working with the XAMPP package this community has been a great resource, though this is my first post.  most of my work has been modifying existing stuff in our network, this is my first project from scratch.

I am working on an access control project where I am storing site names and RFID tags in a table, and using PHP to check for existing records. Basically, I want to submit 2 credentials from a host (site and RFID), check if they exist together in the table, if so return TRUE or 1, if not return FALSE or 0. I am new to both PHP and MySQL (although I took a course years ago) so I am looking here for help.

On this server I have XAMPP installed and everything I need running. I have a table and created a few records for testing. My current trouble is getting PHP to return the proper values for the SQL query. The SQL query works fine, I think - it returns the number of rows that match and that record.  I'm not sure this is the result I need to be able to deal with the way I want.  Using other resources my current PHP statement looks like this:
Code: [Select]
if(mysql_num_rows(mysql_query("SELECT * FROM `users` WHERE 'site_name' LIKE 'berland' AND 'card_id' LIKE '290093C84E' LIMIT 0 , 30"))>0){echo '1';}
   else
   {echo '0';} So, my table has the entry as noted in this query above. Final one will be dynamic where the host will be sending those values - I will likely need help with that as well, but small steps first.;-) Anyway, I was playing around with the end statement >0{echo '1';} but wasn't able to make it do what I want.
Any help is appreciated. Thank you.

Aaron

Hello, I'm having a bit of a problem here, all help to this issues would be much appreciated

I am trying to use text boxes to insert numbers into the database based on what is inputed.
If I have a string, like this for example:
$variable = 09385493;

And I want to insert it into the database like this:
mysql_query("INSERT INTO integers(number)
VALUES ('$variable')");

When checking the integers table in my database, looking at the number field, the $variable that was inserted is outputted as
9385493

Notice the number zero was taken out of the front of the number. If the number is double 0's (009385493), both of those zero's would disappear, too.

Thanks

Hi guys

I have a registration form working fine, my database is as below:

userid
username
email
password
repeatpassword


I have added another column which is "name", users can update their profile once they have logged in so I have created updateprofile.php and when I login-->go to update profile and insert my name nothing adds to mysql name column

this is my code below:

<?php

include ("global.php");


//username session
$_SESSION['username']=='$username';
$username=$_SESSION['username'];

//welcome messaage
echo "Welcome, " .$_SESSION['username']."!<p>";

  if ($_POST['register'])
    {
     //get form data
     $name = addslashes(strip_tags($_POST['name']));

$update = mysql_query("INSERT INTO users (name) VALUES ('$_POST[name]') WHERE username='$username'");
   
}

?>

    <form action='updateprofile.php' method='POST'>
    Company Name:<br />
    <input type='text' name='name'><p />
 
    <input type='submit' name='register' value='Register'>
    </form>


can you please tell me where in this code is wrong? Im new in php so please excuse me if I have silly mistakes.

thanks in advance
 

I don't understand where the empty value is. I've substituted the variables for text and still have the same problem.

Code:
Code: [Select]
$sql = "INSERT INTO courses (course#, name, subject, semester, ap)VALUES('$courseNum', '$courseName', '$subject', '$semester', '$ap')";
Error:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1

I have this code: <?php
$con = mysql_connect("localhost","hhh","hhh");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("hhh", $con);

// --------------------
// Avatar insert check
// --------------------

session_start();

$name = $_POST[name];
$group = $_POST[group];
$age = $_POST[age];
$usernameid = $_SESSION[id];

$result = mysql_query("SELECT * FROM avatars WHERE name='$_POST[name]'");

$num = mysql_numrows($result);

if ($num == 0) {
mysql_query("INSERT INTO avatars (id, usernameid, name, group, age, xp)
VALUES ('', '$usernameid', '$name', '$group', '$age', '0')");

header( 'Location: me/' ) ;
}
else echo 'Sorry, please pick a new name';
?> And it does everything but put the data into the datebase. If I add a session befor and after '$request' they both run, but the sql doesn't. No error returns, if just redirects to the other page. Any help?

I need help badly!

What I want to do is insert into database the value from the selected radio group buttons.. All of them. There are 10 radio groups total (they can be less, but not more).

Thanks!
Code: [Select]
<?php
   require_once('Connections/strana.php'); 
 mysql_select_db($database_strana, $strana);
 
 ?>
<link href="css/styles.css" rel="stylesheet" type="text/css" />

<table width="100%" height="100%" style="margin-left:auto;margin-right:auto;" border="0">
<tr>
<td align="center">
<form action="" method="post" enctype="multipart/form-data" name="form1">
<table>
<?php
$tema = mysql_query("SELECT * from prasanja where tip=2")or die(mysql_error());

function odgovor1($string)
{
$string1 = explode("/", $string);
echo $string1[0];
}
function odgovor2($string)
{
$string1 = explode("/", $string);
echo $string1[1];
}


while ($row=mysql_fetch_array($tema))
{
$id=$row['prasanje_id'];
$prasanje=$row['prasanje_tekst'];
$tekst=$row['odgovor'];
?>
    <tr>
    <td>
    </td>
    </tr>
    <tr>
    <td class="formaP">
 <?php echo $prasanje?>
   </td>
   </tr>
   <tr>
   <td class="formaO">
      <p>
        <label>
          <input type="radio" name="Group<?php echo $id?>" value="<?php odgovor1($tekst) ?>"  />
          <?php odgovor1($tekst) ?></label>
        <br />
        <label>
          <input type="radio" name="Group<?php echo $id?>" value="<?php odgovor2($tekst) ?>" />
          <?php odgovor2($tekst) ?></label>
        <br />
      </p></td>
</tr>
<tr>
<td>
<br />

</td>
</tr>
<?php 




}
?>
</table>
<input align="left"type="submit" name="submit"
value="Внеси" >
</form>
</td>
</tr>
</table>
prasanje = question
tekst/odgovor = answer

The answer table:
id - primary
question_id - the questions ID whose answer is selected in the radio group
user_id - cookie takes care of this
answer - the value from radio group
date - automatic

well this is truely embarrising...i have a insert statement which works within phpmyadmin but when using mysqli_query it returns a error.

INSERT INTO users (username, timestamp) VALUES ('test', UTC_TIMESTAMP())

  Unknown column 'timestamp' in 'field list'   i've been playing about with this for a few hours now ...tried changing the column name (timestamp), adding ` around column names as well as table name.   the column exists which is the strangest part, and ive even checked there is no space after the column name in the db.   whats going on please?  

I have a problem where I am getting these errors after a form has been submitted from join.html
Here are the errors:

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'scswccla'@'localhost' (using password: NO) in /home/scswccla/public_html/insert.php on line 9

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/scswccla/public_html/insert.php on line 9

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: Access denied for user 'scswccla'@'localhost' (using password: NO) in /home/scswccla/public_html/insert.php on line 9

Warning: mysql_real_escape_string() [function.mysql-real-escape-string]: A link to the server could not be established in /home/scswccla/public_html/insert.php on line 9


Here is the insert.php file http://pastebin.com/EVtgidQC
And the join.html file - http://pastebin.com/hqDtGLe5


How would I fix those errors - I got the code from
http://forum.hey0.net/showthread.php?tid=2509

The form is at scswc.com/join.html if you want to look at  it.


I really want to get this working so if you need any more info to help fix it just ask

thanks!

Hi

I have a XML file as follows:

<?xml version="1.0"?>
<inspection_form>
  <inspection_type>
    <inspection_area_tlb>yard_and_lot</inspection_area_tlb>
    <inspection_area>Yard and Lot</inspection_area>
    <items>
      <item>
        <item_name>PID Signage/unauthorized sign on pole</item_name>
        <item_value>0</item_value>
      </item>
      <item>
        <item_name>Landscape well maintained</item_name>
        <item_value>0</item_value>
      </item>
</items>
  </inspection_type>
  <inspection_type>
    <inspection_area_tlb>pump_island</inspection_area_tlb>
    <inspection_area>Pump Island and Canopies</inspection_area>
    <items>
      <item>
        <item_name>pumps clean and free of dirt</item_name>
        <item_value>0</item_value>
      </item>
      <item>
        <item_name>Approved trash cans/clean</item_name>
        <item_value>0</item_value>
      </item>
</items>
  </inspection_type>
</inspection_form>

I want to insert into DB as follows:

inspection_area_tlb inspection_area item_name item_value yard_and_lot yard and Lot PID Signage/unauthorized sign on pole 0 yard_and_lot yard and Lot Landscape well maintained 0 pump_island Pump Island and Canopies pumps clean and free of dirt 0 pump_island Pump Island and Canopies Approved trash cans/clean 0
I have written some php code. But every item node as insert for every 'inspection_type'. This is my code


$filename="sample.xml";
if(filesize($filename)>0)
{
$oDOM = new DOMDocument();
$oDOM->loadXML(file_get_contents($filename)); 
foreach ($oDOM->getElementsByTagName('inspection_type') as $oBookNode)
{
foreach ($oDOM->getElementsByTagName('item') as $itmNode)
{
    $sSQL = sprintf(
        "INSERT INTO inspections_master_tablename_import (INSPECTION_TYPE_DB_C_NAME, INSPECTION_TYPE_C_NAME, INSPECTION_TYPE_ITEM_C_NAME,INSPECTION_TYPE_ITEM_VALUE_C_NAME) VALUES ('%s', '%s', '%s', '%s')",
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area_tlb')->item(0)->nodeValue),
        mysql_real_escape_string($oBookNode->getElementsByTagName('inspection_area')->item(0)->nodeValue),
        mysql_real_escape_string($itmNode->getElementsByTagName('item_name')->item(0)->nodeValue),
mysql_real_escape_string($itmNode->getElementsByTagName('item_value')->item(0)->nodeValue)
    );
    $rResult = mysql_query($sSQL);
    
    if(mysql_errno() > 0)
    {
        printf(
            '<h4 style="color: red;">Query Error:</h4>
            <p>(%s) - %s</p>
            <p>Query: %s</p>
            <hr />',
            mysql_errno(),
            mysql_error(),
            $sSQL
        );
    }
}
}
}


Can anyone help me pls.

I have a Form on registration.html through which i trying to get data in mysql through the below php script but there is and mysql syntax error please help me with the below code.

Code: [Select]
<?php

$conn = mysql_connect("localhost", "onlinewe_meghraj", "password123")
or die(mysql_error());
$db = mysql_select_db("onlinewe_college")
or die(mysql_error());


$name1 = $_POST['name1'];
$name2 = $_POST['name2'];
$year = $_POST['year'];
$department = $_POST['deparment'];
$group = $_POST['group'];
$in_name = $_POST['in_name'];
$in_address = $_POST['in_address'];
$phone = $_POST['phone'];
$email = $_POST['email'];
$mobile1 = $_POST['mobile1'];
$mobile12 = $_POST['mobile2'];
$comment = $_POST['comment'];



$result=mysql_query("INSERT INTO register (name1, name2, year, department, group, in_name, in_address, phone, email, mobile1, mobile2, date, comment) VALUES ('$name1', '$name2', '$year', '$department', '$group', '$in_name', '$in_address', '$phone', '$email', '$mobile1', '$mobile2', '".date("Y-m-d h:i:s")."', '$comment')")


or die("Insert Error: ".mysql_error());

echo "REGISTRATION DONE";


?>
Please reply. Thank you.

I am attempting something similar to the following:

Code: [Select]
mysql_query("UPDATE table SET name='$name' WHERE id=$id");
if (mysql_affected_rows()==0) {
mysql_query("INSERT INTO table (id, name) VALUES ('$name',$id);
}

If the $id row does not exists in the table, mysql_affected_rows() returns 0 and a new $id row gets inserted
but if the $id row already exists and UPDATE changes nothing, mysql_affected_rows() still returns 0
and gives an 'duplicate id' error as expected.

I know I could use a SELECT to test for the existance of the $id row.
In Perl there is an '0E0', 'zero but true', condition to handle this. Is there an equivilant in PHP?
 

I am trying to create a script that takes information from a form and puts in a database.
In the action page, I decided to post a URL that shows the user there story that they posted. This is where I ran into the problem. . I realized that for the optional fields I could not just use a seperate insert statement, because this creates a new row. So I desided to use update statments, but this STILL does not work, they values or simply not getting inserted.  Here is the code:
Code: [Select]
<?php

if(isset($_POST['hidden']))
{

die('SPAM BOT!');
}
if (
!isset($_POST['title']) 
&& !isset($_POST['summary'])
&& !isset($_POST['story'])
&& !isset($_POST['rating'])
&& !isset($_POST['cat'])
)
{
die("<div id='impor'>You forgot to enter one(or more) of the following fields <br /> 
1. Title <br />
2. Summary <br />
3. Story<br />
 </div>
 ");

}


mysqlConnect();


//take data from form an\ put them in variable
$title_form = bb(mysql_real_escape_string($_POST['title'])); //required 
$summ_form = bb(mysql_real_escape_string($_POST['summary']));// required
$story_form = bb(mysql_real_escape_string($_POST['story'])); 
$cat_form = $_POST['cat'];
$rating_form = $_POST['rating'];
$username = $_SESSION['user'];
// Make the other var into a list of links




mysql_query("
INSERT INTO story_info (title, sum, story, user, cat, rating)
VALUES('$title_form','$summ_form', '$story_form,', '$username', '$cat_form','$rating_form')
");
if(isset($_POST['notes']))
{
$notes_form = mysql_real_escape_string($_POST['notes']);
$notes_final = bb($notes_form);
mysql_query("
UPDATE story_info
SET notes = '$notes_final'
WHERE story = '$story_form' AND user = '$username' AND sum = '$summ_form' AND title = '$title_form'
");
}
//put other in array. Use while loop to put link code. Then but it back into one non array variable
if(isset($_POST['u_id']))
{
$uid = mysql_real_escape_string($_POST['u_id']);
$uid_db = str_replace(' ','_', $uid);
$blerg = "
UPDATE story_info
    SET series_id = '$uid_db'
WHERE story = '$story_form' AND user = '$username' AND sum = '$summ_form' AND title = '$title_form'
";
mysql_query($blerg);

}
echo "<h1> Your Story Has Been Posted! Thanks for posting $username .   </h1>";
echo "Please review the post below <br />";
echo "<h2> $title_form </h2>";
echo "<strong> <h2> Summary: </h2> </strong> $summ_form";
echo "<h4> Story: </h4>";
echo "$story_form";
if(isset($notes))
{
echo "<h4> Author's Notes: </h4> ";
echo "$notes_final";


}
if (isset($uid_db))
{
echo '<h3> Unique Series ID </h3>';
echo '<p> Make sure to write down this! <br />' .$uid_db .'</p> ';
}

$db = mysql_query("
SELECT story_id
FROM story_info
WHERE story='$story_form' AND user='$username'
")or die(mysql_error());
$rows = mysql_fetch_assoc($db);

$id = $rows['story_id'];





echo "Catagory: $cat_form <br />
Rating: $rating_form  <br />
";
echo "<a href='?p=page&id=$id'> Click here to view your story! </a>'";
?>


Please help!

I've got

Code: [Select]
for ($i=1; $i<=5; $i++)
{
if(isset($_POST['partsusedqty'.$i]) && $_POST['partsusedqty'.$i] != "" && $_POST['partsusedqty'.$i] != "0.00") {
mysql_query("INSERT INTO partsused (ptnumber, partqty, partdesc, partprice)
VALUES ($ticket, '$partsusedqty'.$i, '$partsuseddesc'.$i, '$partsusedprice'.$i)")
or die(mysql_error()); }
}
I need to know the correct formatting to put these variable variables as values in the mysql query. With this particular code, I get the error "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.1, ''.1, ''.1)' at line 2" I've tried formatting this an endless number of ways, but I used this particular example because its the one I really thought should work. Everything I've tried that doesn't throw an error put the $partsusedqty in the partqty, partdesc, and partprice fields. Thanks for any help!