php check mysql

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • anfetienne
    Contributor
    • Feb 2009
    • 424
    #1

    php check mysql

    hi,

    I have a database that i've made to store html.....the id of each row is a random generated number.....the id along with the html is inputted into the database.

    how can i use php to check whether there is a certain id already in the database and if it exists then overwrite the row of the id???

    thanks
    Tags: None
    • Markus
      Recognized Expert Expert
      • Jun 2007
      • 6092
      #2
      When someone says 'ID in database' it is usually an auto-increment column. You don't need to randomly generate a number, but I won't go into that.

      You can query a database for a specific value using the WHERE clause and then to overwrite the row that is matched, you can use an UPDATE query.

      Code:
      // Select column 1 from table name where column name = $your_var.
$sql = "SELECT `col1` FROM `tbl_name` WHERE `clmn_name` = '{$your_var}'"
// If mysql_query returns false, we'll die with the error.
$res = mysql_query( $sql ) or die( mysql_error );

// If a there is a match
if ( mysql_num_rows( $res ) > 0 )
{
   // Do update.
}
else
{
    echo "No matches.";
}
      UPDATE
      WHERE

        Comment

        • anfetienne
          Contributor
          • Feb 2009
          • 424
          #3
          i know auto increment would be a good way but as the process im working on is based on the random number as the id of the template its better to use the template id.......thanks for the help markus.....ill post the results

            Comment

            • Markus
              Recognized Expert Expert
              • Jun 2007
              • 6092
              #4
              Originally posted by anfetienne
              i know auto increment would be a good way but as the process im working on is based on the random number as the id of the template its better to use the template id.......thanks for the help markus.....ill post the results
              OK, np.

              - Mark.

                Comment

                • anfetienne
                  Contributor
                  • Feb 2009
                  • 424
                  #5
                  i get this message for the coding below

                  Code:
                  <?PHP error_reporting(E_ALL);
$username="tempsUser";
$password="auction";
$database="auctionTemps";

$returnURL = $_POST['returnURL'];

$tempID=$_POST['tempID'];
$htmlcss=$_POST['htmlcss'];
$header=$_POST['header'];
$nav=$_POST['nav'];
$titleWrap01=$_POST['titleWrap01'];
$title=$_POST['title'];
$titleWrap02=$_POST['titleWrap02'];
$subheadWrap01=$_POST['subheadWrap01'];
$subheading=$_POST['subheading'];
$subheadWrap02=$_POST['subheadWrap02'];
$flashWrap01=$_POST['flashWrap01'];
$flashWrap02=$_POST['flashWrap02'];
$flash01=$_POST['flash01'];
$flashVar=$_POST['flashVar'];
$flash02=$_POST['flash02'];
$flashWrap03=$_POST['flashWrap03'];
$descriptionWrap01=$_POST['descriptionWrap01'];
$description=$_POST['description'];
$descriptionWrap02=$_POST['descriptionWrap02'];
$generalWrap01=$_POST['generalWrap01'];
$general=$_POST['general'];
$generalWrap02=$_POST['generalWrap02'];
$aboutWrap01=$_POST['aboutWrap01'];
$about=$_POST['about'];
$aboutWrap02=$_POST['aboutWrap02'];
$paymentWrap01=$_POST['paymentWrap01'];
$payment01=$_POST['payment01'];
$payment02=$_POST['payment02'];
$paymentWrap02=$_POST['paymentWrap02'];
$termsWrap01=$_POST['termsWrap01'];
$terms=$_POST['terms'];
$termsWrap02=$_POST['termsWrap02'];
$footer=$_POST['footer'];


// OPEN CONNECTION ---> 
$connection=mysql_connect("localhost" ,"$username", "$password") or die("Unable to connect!");

mysql_select_db("$database") or die("Unable to select database!");

// Select column 1 from table name where column name = $your_var.
$sql = "SELECT id FROM savedTemps WHERE id = '{$tempID}'";
// If mysql_query returns false, we'll die with the error.
$res = mysql_query( $sql ) or die( mysql_error() );
 
// If a there is a match
if ( mysql_num_rows( $res ) > 0 )
{
//  EXECUTE QUERY ---> 
$query="UPDATE savedTemps (
		    htmlcss, 
            header, 
            nav, 
            titleWrap01, 
            title,
			titleWrap02,
            subheadWrap01, 
            subheading,
			subheadWrap02, 
            flashWrap01, 	
            flashWrap02, 
            flash01, 
            flashVar,
            flash02, 
            flashWrap03,
			descriptionWrap01,
            description, 
            descriptionWrap02,
			generalWrap01, 
            general, 
            generalWrap02, 
			aboutWrap01,
            about, 
            aboutWrap02,
			paymentWrap01, 
            payment01, 
            payment02, 
            paymentWrap02,
            termsWrap01, 
            terms, 
            termsWrap02,
			footer)

        VALUES(	
		    '$htmlcss', 
            '$header', 
            '$nav.', 
            '$titleWrap01', 
            '$title',
			'$titleWrap02',
            '$subheadWrap01', 
            '$subheading',
			'$subheadWrap02', 
            '$flashWrap01', 	
            '$flashWrap02', 
            '$flash01', 
            '$flashVar', 
            '$flash02', 
            '$flashWrap03',
			'$descriptionWrap01',
            '$description', 
            '$descriptionWrap02',
			'$generalWrap01', 
            '$general', 
            '$generalWrap02', 
			'$aboutWrap01',
            '$about', 
            '$aboutWrap02',
			'$paymentWrap01', 
            '$payment01', 
            '$payment02', 
            '$paymentWrap02',
            '$termsWrap01', 
            '$terms', 
            '$termsWrap02',
			'$footer')";
}
else
{
//  EXECUTE QUERY ---> 
$query="INSERT savedTemps (

		    id, 
		    htmlcss, 
            header, 
            nav, 
            titleWrap01, 
            title,
			titleWrap02,
            subheadWrap01, 
            subheading,
			subheadWrap02, 
            flashWrap01, 	
            flashWrap02, 
            flash01, 
            flashVar,
            flash02, 
            flashWrap03,
			descriptionWrap01,
            description, 
            descriptionWrap02,
			generalWrap01, 
            general, 
            generalWrap02, 
			aboutWrap01,
            about, 
            aboutWrap02,
			paymentWrap01, 
            payment01, 
            payment02, 
            paymentWrap02,
            termsWrap01, 
            terms, 
            termsWrap02,
			footer)

        VALUES(	
		    '$tempID', 
		    '$htmlcss', 
            '$header', 
            '$nav.', 
            '$titleWrap01', 
            '$title',
			'$titleWrap02',
            '$subheadWrap01', 
            '$subheading',
			'$subheadWrap02', 
            '$flashWrap01', 	
            '$flashWrap02', 
            '$flash01', 
            '$flashVar', 
            '$flash02', 
            '$flashWrap03',
			'$descriptionWrap01',
            '$description', 
            '$descriptionWrap02',
			'$generalWrap01', 
            '$general', 
            '$generalWrap02', 
			'$aboutWrap01',
            '$about', 
            '$aboutWrap02',
			'$paymentWrap01', 
            '$payment01', 
            '$payment02', 
            '$paymentWrap02',
            '$termsWrap01', 
            '$terms', 
            '$termsWrap02',
			'$footer')";
}


//  CLOSE CONNECTION ---> 
mysql_close($connection); 

/////////////////////////////////////////////////////////////////////////////////// 

?>
<script language="JavaScript">
<!--
window.location="<? print $returnURL?>";
//-->
</SCRIPT>

                    Comment

                    • anfetienne
                      Contributor
                      • Feb 2009
                      • 424
                      #6
                      has the use of UPDATE been taken away from mysql 5.0.22?

                      i think i know what the problem is.....I tried to set insead of update

                      Code:
                       // Select column 1 from table name where column name = $your_var.
 $sql = "SELECT `col1` FROM `tbl_name` WHERE `clmn_name` = {$your_var}'"
 // If mysql_query returns false, we'll die with the error.
 $res = mysql_query( $sql ) or die( mysql_error );
  
 // If a there is a match
 if ( mysql_num_rows( $res ) > 0 )
 {
    // Do update.
 }
 else
 {
     echo "No matches.";
 }

                        Comment

                        • TheServant
                          Recognized Expert Top Contributor
                          • Feb 2008
                          • 1168
                          #7
                          No it has not been depreciated. It should be:
                          UPDATE tablename SET tablevar1='$use rvar1', tablevar2='$use rvar2', tablevar3='$use rvar3' WHERE blablabla
                          Check it out here.

                            Comment

                            • anfetienne
                              Contributor
                              • Feb 2009
                              • 424
                              #8
                              i have done (what's below) to make that the post arrays are being populated and they are perfectly fine but if i try even a simple INSERT i still get the error message saying undefined index

                              print( '<pre>' );
                              print_r( $_POST );
                              print( '</pre>' );

                                Comment

                                • TheServant
                                  Recognized Expert Top Contributor
                                  • Feb 2008
                                  • 1168
                                  #9
                                  So what does your MySQL command look like? I don't really see what your array has to do with the undefined index error?

                                  EDIT: This thread is getting mixed with (or run parallel to) this one. Let's sort it out ;)

                                    Comment

                                    • anfetienne
                                      Contributor
                                      • Feb 2009
                                      • 424
                                      #10
                                      i have actually perfected the code yesterday...... it works great, THANKS FOR ALL THE HELP!...here is my finished code.

                                      Code:
                                      <?php
$username="tempsUser";
$password="auction";
$database="auctionTemps";

$rtrnURL = $_POST['rtrnURL'];
$tempID = $_POST['tempID'];
$htmlcss = $_POST['htmlcss'];
$header = $_POST['header'];
$nav = $_POST['nav'];
$titleWrap01 = $_POST['titleWrap01'];
$title = $_POST['title'];
$titleWrap02 = $_POST['titleWrap02'];
$subheadWrap01 = $_POST['subheadWrap01'];
$subheading = $_POST['subheading'];
$subheadWrap02 = $_POST['subheadWrap02'];
$flashWrap01 = $_POST['flashWrap01'];
$flashWrap02 = $_POST['flashWrap02'];
$flash01 = $_POST['flash01'];
$flashVar = $_POST['flashVar'];
$flash02 = $_POST['flash02'];
$flashWrap03 = $_POST['flashWrap03'];
$descriptionWrap01 = $_POST['descriptionWrap01'];
$description = $_POST['description'];
$descriptionWrap02 = $_POST['descriptionWrap02'];
$generalWrap01 = $_POST['generalWrap01'];
$general = $_POST['general'];
$generalWrap02 = $_POST['generalWrap02'];
$aboutWrap01 = $_POST['aboutWrap01'];
$about = $_POST['about'];
$aboutWrap02 = $_POST['aboutWrap02'];
$paymentWrap01 = $_POST['paymentWrap01'];
$payment01 = $_POST['payment01'];
$payment02 = $_POST['payment02'];
$paymentWrap02 = $_POST['paymentWrap02'];
$termsWrap01 = $_POST['termsWrap01'];
$terms = $_POST['terms'];
$termsWrap02 = $_POST['termsWrap02'];
$footer = $_POST['footer'];

// OPEN CONNECTION ---> 
$connection=mysql_connect("localhost" ,"$username", "$password") or die("Unable to connect!");

mysql_select_db("$database") or die("Unable to select database!");

// Select column 1 from table name where column name = $your_var.
$sql = "SELECT * FROM savedTemps WHERE tempID = '{$tempID}'";
// If mysql_query returns false, we'll die with the error.
$res = mysql_query( $sql ) or die( mysql_error );
 
// If a there is a match
if ( mysql_num_rows( $res ) > 0 )
{

$query="

UPDATE savedTemps
SET title='{$title}', subheading='{$subheading}', description='{$description}', general='{$general}', about='{$about}', terms='{$terms}' WHERE tempID='{$tempID}'";
}
else
{
//  EXECUTE QUERY ---> 
$query="INSERT savedTemps (

		    tempID, 
		    htmlcss, 
            header, 
            nav, 
            titleWrap01, 
            title,
			titleWrap02,
            subheadWrap01, 
            subheading,
			subheadWrap02, 
            flashWrap01, 	
            flashWrap02, 
            flash01, 
            flashVar,
            flash02, 
            flashWrap03,
			descriptionWrap01,
            description, 
            descriptionWrap02,
			generalWrap01, 
            general, 
            generalWrap02, 
			aboutWrap01,
            about, 
            aboutWrap02,
			paymentWrap01, 
            payment01, 
            payment02, 
            paymentWrap02,
            termsWrap01, 
            terms, 
            termsWrap02,
			footer)

        VALUES(	
		    '".$tempID."', 
		    '".$htmlcss."', 
            '".$header."', 
            '".$nav."', 
            '".$titleWrap01."', 
            '".$title."',
			'".$titleWrap02."',
            '".$subheadWrap01."', 
            '".$subheading."',
			'".$subheadWrap02."', 
            '".$flashWrap01."', 	
            '".$flashWrap02."', 
            '".$flash01."', 
            '".$flashVar."', 
            '".$flash02."', 
            '".$flashWrap03."',
			'".$descriptionWrap01."',
            '".$description."', 
            '".$descriptionWrap02."',
			'".$generalWrap01."', 
            '".$general."', 
            '".$generalWrap02."', 
			'".$aboutWrap01."',
            '".$about."', 
            '".$aboutWrap02."',
			'".$paymentWrap01."', 
            '".$payment01."', 
            '".$payment02."', 
            '".$paymentWrap02."',
            '".$termsWrap01."', 
            '".$terms."', 
            '".$termsWrap02."',
			'".$footer."')";
}

			
//////-----> 
$result=mysql_query($query) or die("Error in query:".mysql_error()); 
//if ($result) 
    //echo mysql_affected_rows()." row inserted into the database effectively."; 

//  CLOSE CONNECTION ---> 
mysql_close($connection); 

/////////////////////////////////////////////////////////////////////////////////// 

?>
<script language="JavaScript">
<!--
window.location="<? print $rtrnURL?>";
//-->
</SCRIPT>

                                        Comment

                                        • TheServant
                                          Recognized Expert Top Contributor
                                          • Feb 2008
                                          • 1168
                                          #11
                                          Great to hear you got it working. In a nutshell how did you fix it?

                                            Comment

                                            • anfetienne
                                              Contributor
                                              • Feb 2009
                                              • 424
                                              #12
                                              hi, well for starters there was nothing wrong with the post array so i didnt have to make any changes to the form.

                                              the connection and sql code looks like this instead of selectiong a 1 column i used SELECT *
                                              Code:
                                              $connection=mysql_connect("localhost" ,"$username", "$password") or die("Unable to connect!");

mysql_select_db("$database") or die("Unable to select database!");

// Select column 1 from table name where column name = $your_var.
$sql = "SELECT * FROM savedTemps WHERE tempID = '{$tempID}'";
// If mysql_query returns false, we'll die with the error.
$res = mysql_query( $sql ) or die( mysql_error );
                                              for the code that runs the sql update if and else was suggested so running by that i basically only chose to update the fields that needed updating....i had to enclose all the variables as values like this '{$var}' as seen below

                                              Code:
                                              if ( mysql_num_rows( $res ) > 0 )
{

$query="

UPDATE savedTemps
SET title='{$title}', subheading='{$subheading}', description='{$description}', general='{$general}', about='{$about}', terms='{$terms}' WHERE tempID='{$tempID}'";
}
                                              and for this else this was to basically input all form data instead of some into the database incase they did not already exist....i had to go from single quotes back to '".$var."' as it would not see the $var as values for some reason.

                                              Code:
                                              else
{
//  EXECUTE QUERY ---> 
$query="INSERT savedTemps (

		    tempID, 
		    htmlcss, 
            header, 
            nav, 
            titleWrap01, 
            title,
			titleWrap02,
            subheadWrap01, 
            subheading,
			subheadWrap02, 
            flashWrap01, 	
            flashWrap02, 
            flash01, 
            flashVar,
            flash02, 
            flashWrap03,
			descriptionWrap01,
            description, 
            descriptionWrap02,
			generalWrap01, 
            general, 
            generalWrap02, 
			aboutWrap01,
            about, 
            aboutWrap02,
			paymentWrap01, 
            payment01, 
            payment02, 
            paymentWrap02,
            termsWrap01, 
            terms, 
            termsWrap02,
			footer)

        VALUES(	
		    '".$tempID."', 
		    '".$htmlcss."', 
            '".$header."', 
            '".$nav."', 
            '".$titleWrap01."', 
            '".$title."',
			'".$titleWrap02."',
            '".$subheadWrap01."', 
            '".$subheading."',
			'".$subheadWrap02."', 
            '".$flashWrap01."', 	
            '".$flashWrap02."', 
            '".$flash01."', 
            '".$flashVar."', 
            '".$flash02."', 
            '".$flashWrap03."',
			'".$descriptionWrap01."',
            '".$description."', 
            '".$descriptionWrap02."',
			'".$generalWrap01."', 
            '".$general."', 
            '".$generalWrap02."', 
			'".$aboutWrap01."',
            '".$about."', 
            '".$aboutWrap02."',
			'".$paymentWrap01."', 
            '".$payment01."', 
            '".$payment02."', 
            '".$paymentWrap02."',
            '".$termsWrap01."', 
            '".$terms."', 
            '".$termsWrap02."',
			'".$footer."')";
}

			
//////-----> 
$result=mysql_query($query) or die("Error in query:".mysql_error()); 
//if ($result) 
    //echo mysql_affected_rows()." row inserted into the database effectively."; 

//  CLOSE CONNECTION ---> 
mysql_close($connection); 

/////////////////////////////////////////////////////////////////////////////////// 

?>

                                                Comment

                                                Previous template Next
                                                Working...