n00b - simple script to pull and show sql variable

bleemster · February 9, 2018

I understand from all languages we have to define a variable. even php the define command is used...

Can someone show me a simple script to pull the ID field from a DUMMY sql table and then display it.

i have a connection.php page defined and connects correctly.

table = DUMMY

fields = ID, name, email

I want a page to say:

<body>

<H2>Welcome to page [iD]</h2>

</body>

Thank you in advance.

cyberRobot · February 9, 2018

Which MySQL API are you using (PDO, MySQLi, or the deprecated MySQL)? Note that there are a number of examples in the PHP manual.

Edit: I should also ask, what have you tried?

cyberRobot · February 9, 2018

Perhaps the following page will help:

http://php.net/manual/en/mysqlinfo.api.choosing.php

It has examples for all three APIs. The examples show how to connect with the database, run a query, and display a value from the database.

Note the message about the mysql_* functions being deprecated. :happy-04:

bleemster · February 9, 2018

currently MySQLI - i will check the link - thank you

bleemster · February 9, 2018

I want the "thunderball created successfully" to have a message "your ID is ID=[iD from the newly created table entry]" But cant seem to pick up the ID as im guessing it hasnt been created and passed to SQL yet

here is my script

<?php

error_reporting(E_ALL);
ini_set('display_errors', '1');





session_start();
if(!isset($_SESSION['username'])){
   header("Location:login.php");
}
require('connect.php');


    if (isset($_POST['submit']))
    {
        
        $creator = mysqli_real_escape_string($connection, $_POST['creator']);
	    $coin = mysqli_real_escape_string($connection, $_POST['coin']);
	    $information = mysqli_real_escape_string($connection, $_POST['information']);
	    $price = mysqli_real_escape_string($connection, $_POST['price']);
        $rundate = date('d/m/Y h:i:s', time());
        $date = date('d/m/Y h:i:s', time());
    

        $query = "INSERT INTO `thunderball` (creator, coin,information,price,photo1,photo2,photo3,DATESTAMP,rundate) 
        VALUES ('$creator', '$coin','$information','$price','','','','$date','$rundate')";
        $result = mysqli_query($connection, $query);
        
       
        $sql = "SELECT ID FROM thunderball  ORDER BY ID DESC LIMIT 1";
         
        $rs = mysqli_query($connection,$sql);
        $row = mysqli_fetch_array($rs);
            
        $id =  $row['ID'];
         
         if($_FILES['photo1']['name'] !='')
         {
             $temp = explode(".", $_FILES["photo1"]["name"]);
            $newfilename = "ID".$row['ID']."a". '.' . end($temp);
            move_uploaded_file($_FILES["photo1"]["tmp_name"], "images/photos/" . $newfilename);
            
            $sql = "Update thunderball SET photo1 = '$newfilename' Where ID='$id'";
            mysqli_query($connection,$sql);
            
         }
         
         if($_FILES['photo2']['name'] !='')
         {
             $temp = explode(".", $_FILES["photo2"]["name"]);
            $newfilename = "ID".$row['ID']."b". '.' . end($temp);
            move_uploaded_file($_FILES["photo2"]["tmp_name"], "images/photos/" . $newfilename);
            
            $sql = "Update thunderball SET photo2 = '$newfilename' Where ID='$id'";
            mysqli_query($connection,$sql);
            
         }
         
         if($_FILES['photo3']['name'] !='')
         {
             $temp = explode(".", $_FILES["photo3"]["name"]);
            $newfilename = "ID".$row['ID']."c". '.' . end($temp);
            move_uploaded_file($_FILES["photo3"]["tmp_name"], "images/photos/" . $newfilename);
            
            $sql = "Update thunderball SET photo3 = '$newfilename' Where ID='$id'";
            mysqli_query($connection,$sql);
            
         }  
           
           
        if($result){
            
            $smsg = "Thunderball Event Created Successfully.";
        }else{
            $fmsg ="Thunderball Event Creation Failed";
        }
    }
    
    


    
     $sql1 = "select fullname from user WHERE USERNAME='{$_SESSION['username']}'";
     $result1 = mysqli_query($connection,$sql1);
     $row1 = mysqli_fetch_array($result1);
     
     $fullname = $row1['fullname'];
     
     //echo $fullname;
     
    ?>
    
    
    
    
    
<html>
<head>
	<title>TB Creation Wizard</title>
	
<!-- Latest compiled and minified CSS -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" >

<!-- Optional theme -->
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap-theme.min.css" >

<link rel="stylesheet" href="styles.css" >

<!-- Latest compiled and minified JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>




<body>




<div class="container">
      <form class="form-signin" method="POST" enctype="multipart/form-data">
      
      <?php if(isset($smsg)){ ?><div class="alert alert-success" role="alert"> <?php echo $smsg; ?> </div><?php } ?>
      <?php if(isset($fmsg)){ ?><div class="alert alert-danger" role="alert"> <?php echo $fmsg; ?> </div><?php } ?>
        <h2 class="form-signin-heading">Create a TB Event</h2>
        
        
        

       

        <input type="hidden" name="creator" id="inputCreator" class="form-control" placeholder="Creator" value="<?php echo $fullname; ?>" >
        <input type="text" name="creator" id="inputCreator" class="form-control" placeholder="Creator" value="<?php echo $fullname; ?>" disabled>
        

        <input type="text" name="coin" id="inputCoin" class="form-control" placeholder="Coin on offer (E.g. 50p)" required autofocus>
        
        
        <input type="text" name="information" id="inputInformation" class="form-control" placeholder="Coin Infomation (E.g. Kew Gardens)" required autofocus>
        
        
        <input type="text" name="price" id="inputPrice" class="form-control" placeholder="Price of each Ball (E.g. £5.50)" required autofocus>
        
        
        <input type="file" name="photo1" id="inputPhoto1" class="form-control" placeholder="Photo1 of the Coin"  autofocus>
        
        
        <input type="file" name="photo2" id="inputPhoto2" class="form-control" placeholder="Photo2 of the Coin"  autofocus>
        
        
        <input type="file" name="photo3" id="inputPhoto3" class="form-control" placeholder="Photo3 of the Coin"  autofocus>
        


        <!--<input type="date" id="myDate"  />-->
            <script type="text/javascript">
            function SetDate()
            {
            var date = new Date();
            var day = date.getDate();
            var month = date.getMonth() + 1;
            var year = date.getFullYear();
            if (month < 10) month = "0" + month;
            if (day < 10) day = "0" + day;
            var today = year + "-" + month + "-" + day;
            document.getElementById('myDate').value = today;
            }
            </script>
           <!-- <body onload="SetDate();">
            <br><br>-->

        <!--<input type="date" name="rundate" id="inputRunDate" class="form-control" placeholder="Run Date" required autofocus>
        <br>-->
        
        <br>
        <input class="btn btn-lg btn-primary btn-block" type="submit" value="Create" name="submit">
        <br>
        <a class="btn btn-lg btn-primary btn-block" href="login.php">Home</a>
        <a class="btn btn-lg btn-primary btn-block" href="logout.php">Logoff</a>
      </form>
</div>


</body>
</html>

Psycho · February 9, 2018

I would highly advise you to switch to the PDO API for wortking iwth your database. It is much easier to use than MySqli in my (and many others) opinion. Also, use prepared statements as opposed to escaping values.

As to your code, it is kind of confusing and has a lot of issues:

1. Why do you do an INSERT and then run three different UPDATE queries for the three photos? You should just process all the submitted data and do a single INSERT. But, you also should not have fields such as photo1, photo2, photo3, etc. When you have multiple values tied to a single entity, that data should go into a separate table as individual records with a foreign key reference back to the main entity.

2. There is no error checking. have you verified that your records are getting inserted?

3. No need to create a date in PHP (i.e. your DATESTAMP field), just use the NOW() MySQL function, or better yet, you can set a field up to automatically add the data when a record is inserted or even update the field whenever the record is updated. That way you don't even need to include the field int he ISERT or UPDATE queries.

4. This i a very poor way to get the ID of the record that was just inserted.

$sql = "SELECT ID FROM thunderball ORDER BY ID DESC LIMIT 1";

A race condition could allow a second user to insert a record before the first user selects the ID. That is another reason why doing a single INSERT is better int his situation.

5. The check to see if the record was adding by checking $result only tells you if the first INSERT completed. The photo inserts could have failed. yet another reason not to do them separately

6.. Assuming adding the photos individually was the right approach, which it is not, you should not duplicate the code. Create a loop and change the record to be processed. Otherwise, if you make a change you have to make sure you change all instances of the duplicated code.

As to your specific problem, there are a few things I could see going wrong:

1. The INSERT is not completing successfully. You should be able to verify if this is the case by checking the DB table. If it isn't you will need to figure out why. Hint: Adding error handling would be a good idea.

2. Assuming the INSERT is working the query to get the ID is failing. The only way I can see this happening is if there is no ID field.

3. If the ID field does exist, maybe you didn't set it as an auto-increment integer field. If so, every record would have an empty value as the ID. Again, you can verify this by checking the table

4. With the Mysqli API, you can get the last INSERT ID with the built in function: mysqli_insert_id. This will prevent any problems with race conditions.

bleemster · February 9, 2018

hi psycho thank you for your detailed reply.

70% of what you suggested is WAY over my head and im trying to learn and this has been built over SO much research so expect there are a lot of conflicts and errors etc.

As it is at the time of the code below:

the insert of data into the thunderball table does insert into the SQL DB correctly
the ID field is there
the ID field does auto increment
if i left the script alone as it is i could live with it but this is to learn why some of the stuff ive found on the internet says "do this and do that" and it doesnt work !!! - all i want to do is print the ID and figure out why its not working.
the photo part was a right pain in the ass to complete. As you can see for housekeeping purposes i want all the photos named with the ID of the game ID which is the same ID im trying to print (and failing) and calling photo1 as "a" e.g. ID123a.jpg
i just dont get why i know the ID variable is obviously set as shown in the photo function, but when i try to echo the $ID again it says that its not been set!!!!!!!!! Melting my brain

I am more than happy to replace the SQLI to PDO, do i just do a find and replace? do the commands associated with PDO differ from the SQLI?

cyberRobot · February 9, 2018

As Psycho was referring to, you can replace this

$sql = "SELECT ID FROM thunderball  ORDER BY ID DESC LIMIT 1";
$rs = mysqli_query($connection,$sql);
$row = mysqli_fetch_array($rs);
$id =  $row['ID'];

With this

$id = mysqli_insert_id($connection);

Note: that single line replaces all four lines. You don't need to run a separate query to get the ID of last record added. MySQL handles that for you.

If you output $id, you should get the ID you are looking for. And you should be able to change this

$smsg = "Thunderball Event Created Successfully.";

To something like this

$smsg = "Thunderball Event Created Successfully. Your ID is " . htmlentities($id);

Sign In

n00b - simple script to pull and show sql variable

Recommended Posts

bleemster

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

bleemster

Link to comment

Share on other sites

bleemster

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

bleemster

Link to comment

Share on other sites

cyberRobot

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information