htmlcinco

# August 18, 2014 at 8:37 pm

i’m practicing PHP and am trying hard to learn using the latest methods. All i’m trying to do is upload these two inputs to the mysql database(which is ready) using the new PDO method instead of the deprecated methods. Can someone point me to the right tutorial or show me how to do this?

    <form action="upload.php" method="post">

        <input type="text/css" name="cover-url" placeholder="Movie Cover URL" /> <br />
        <input type="text/css" name="title" placeholder="Movie Title" /> <br />
        <input type="submit" name="upload" />

    </form>

__

# August 18, 2014 at 9:34 pm

Here’s the man page.

/* assuming:
$host = database hostname
$database = database name
$username = database username
$password = database password
*/
try{
    // connect to the database.
    $dsn = "mysql:host=$host;database=$database;charset=utf8";
    $PDO = new pdo( $dsn,$username,$password );

    // prepare a statement for insert
    $stmt = $PDO->prepare( "insert into table_name( url,title ) values( :url,:title )" );

    // insert using values from post
    if( $stmt->execute( array( 'url'=>$_POST['url'],'title'=>$_POST['title'] ) ) ){
        //  success!
    }
    else{
        // failure!
    }
}
catch( PDOException $PDOe ){
    /*  PDO error handling goes here  */
}

htmlcinco

# August 19, 2014 at 12:13 am

`
<?php

//submit data to database
if(isset($_POST['upload'])) {

    $title = $_POST['title'];
    $cover_url = $_POST['cover-url'];
    $starring = $_POST['starring'];
    $director = $_POST['director'];
    $run_time = $_POST['runtime'];
    $released = $_POST['released'];
    $rating = $_POST['rating'];
    $plot_summary = $_POST['plot-summary'];
    $personal_opinion = $_POST['personal-opinion'];
    $trailer = $_POST['trailer'];

    try {

        include("host-connect.php");

        // prepare statement for insert
        $insert = $connect -> prepare("INSER INTO movies(TITLE, COVER-URL, STARRING, DIRECTOR, RUNTIME, RELEASED, RATING, PLOT-SUMMARY, PERSONAL-OPINION, TRAILER) VALUES (:title, :cover_url, :starring :director, :run_time, released, :rating, :plot_summary, :personal_opinion, :trailer)");

        // insert using values from post
        if( $insert -> execute( array(
            'TITLE'=>$title,
            'COVER-URL'=>$cover_url,
            'STARRING'=>$starring,
            'DIRECTOR'=>$director,
            'RUNTIME'=>$run_time,
            'RELEASED'=>$released,
            'RATING'=>$rating,
            'PLOT-SUMMARY'=>$plot_summary,
            'PERSONAL-OPINION'=>$personal_opinion,
            'TRAILER'=>$trailer,
        ) ) ) {

        }

    } catch( PDOException $e ) {

        echo $e -> getMessage();

    }

}

?>
`

I followed your code and tested it it but im getting this error
SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

I searched google and stack overflow but none of the solutions worked.

what values is VALUES (:title, :cover_url, :starring :director, :run_time, released, :rating,
refering to? the ones in the form fields or the ones in the mysql database?

__

# August 19, 2014 at 12:19 am

what values is VALUES( … ) … refering to?

They are placeholders for the values you will provide later (in this case, from the form).

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

In your code above, I am assuming you meant released to be a parameter marker. However, it is missing its leading colon, and so MySQL thinks it is an identifier (i.e., a column name). In the end, there are only nine parameter markers in the statement, but you pass ten values.

tl;dr: released should be :released.

htmlcinco

# August 19, 2014 at 12:58 am

I fixed that and the missing comma and the error changed to Invalid parameter number: parameter was not defined although i triple checked and they all match. Does it mean its not matching with the database? I have an ID on the mysql table but didnt include it in the php. Would that be the issue?

__

# August 19, 2014 at 8:35 am

Looks like you’re using different case and dashes instead of underscores in many places (e.g., COVER-URL vs. cover_url). Make everything match and try again. (Sorry I didn’t think of this last night; I was half-asleep.)

htmlcinco

# August 19, 2014 at 12:14 pm

I fixed that and also i had INSER instead of INSERT. But now im getting all as “undefined index” and Invalid parameter number: parameter was not defined. Not sure whats causing this error.

`
<?php

//submit data to database
if(isset($_POST['upload'])) {

    $title = $_POST['TITLE'];
    $cover_url = $_POST['COVER_URL'];
    $starring = $_POST['STARRING'];
    $director = $_POST['DIRECTOR'];
    $run_time = $_POST['RUNTIME'];
    $released = $_POST['RELEASED'];
    $rating = $_POST['RATING'];
    $plot_summary = $_POST['PLOT_SUMMARY'];
    $personal_opinion = $_POST['PERSONAL_OPINION'];
    $trailer = $_POST['TRAILER'];

    try {

        include("host-connect.php");

        // prepare statement for insert
        $insert = $connect -> prepare("INSERT INTO movies(TITLE, COVER_URL, STARRING, DIRECTOR, RUNTIME, RELEASED, RATING, PLOT_SUMMARY, PERSONAL_OPINION, TRAILER) VALUES (:TITLE, :COVER_URL, :STARRING, :DIRECTOR, :RUN_TIME, :RELEASED, :RATING, :PLOT_SUMMARY, :PERSONAL_OPINION, :TRAILER)");

        // insert using values from post
        if( $insert -> execute( array(
            'TITLE'=>$title,
            'COVER_URL'=>$cover_url,
            'STARRING'=>$starring,
            'DIRECTOR'=>$director,
            'RUNTIME'=>$run_time,
            'RELEASED'=>$released,
            'RATING'=>$rating,
            'PLOT_SUMMARY'=>$plot_summary,
            'PERSONAL_OPINION'=>$personal_opinion,
            'TRAILER'=>$trailer
        ) ) ) {

        }

    } catch( PDOException $e ) {

        echo $e -> getMessage();

    }

}

?>

`

htmlcinco

# August 19, 2014 at 12:23 pm

Turns out i changed the name of the submit button when making the names match and so i had a capital button name on one, and not on the other. But i’m still getting the
SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

chrisburton

# August 19, 2014 at 12:47 pm

@Jarolin It looks like it should be :RUNTIME for :RUN_TIME in your prepared statement.

Change it to this:

// prepare statement for insert
    $insert = $connect->prepare("INSERT INTO movies(TITLE, COVER_URL, STARRING, DIRECTOR, RUNTIME, RELEASED, RATING, PLOT_SUMMARY, PERSONAL_OPINION, TRAILER) VALUES (:TITLE, :COVER_URL, :STARRING, :DIRECTOR, :RUNTIME, :RELEASED, :RATING, :PLOT_SUMMARY, :PERSONAL_OPINION, :TRAILER)");

chrisburton

# August 19, 2014 at 1:00 pm

@Jarolin Oh and as traq mentioned, be consistent with naming. If you’re more able to remember not using spaces for words (RUNTIME) then use that or vice versa with underscores.

htmlcinco

# August 19, 2014 at 1:18 pm

Thank you guys its working perfectly now. Now i just need to understand whats going on here and try to memorize it. Thanks @TRAQ and @ChrisBurton

chrisburton

# August 19, 2014 at 2:52 pm

If you want to learn how things work in PHP, try rewriting the code above (since it’s quite short) and using the docs if you don’t understand what is going on.

htmlcinco

# August 20, 2014 at 7:37 am

@chrisburton Thanks ill do that.

Instead of bombarding this forum with PHP questions i cant find answers to, i was thinking of just posting them on this one forum. So heres another question.

I managed to display the data thats on my database onto the page and the following code works perfectly without any problems, but i’m sure some improvements can be made to make it more reliable/readable. So i was hoping you guys can help me with that.

`
<?php

// Include database connection file
include("php/db-connect.php");

try {

    // get database data
    $sql = "SELECT TITLE, COVER_URL FROM movies ORDER BY id DESC";
    $q = $connect -> query($sql);
    $q -> setFetchMode(PDO::FETCH_ASSOC);

} catch( PDOException $e ) {

    $e -> getMessage();
    echo "Could not retrieve data from the database";
    die();

}

                <!-- movies -->
                <div class="movies-library-container">
                    <?php while ($row = $q->fetch()): ?>
                        <div><a href="movie.php"><img src="<?php echo $row['COVER_URL']; ? />" alt="<?php echo $row['TITLE']; ?>" width="151" height="227" class="cover-image"/><h4><?php echo $row['TITLE']; ?></h4></a></div>
                    <?php endwhile; ?>
                </div>

__

# August 20, 2014 at 10:59 am

Instead of bombarding this forum with PHP questions i cant find answers to, i was thinking of just posting them on this one forum. So heres another question.

Two thoughts on that:

• makes thread navigation harder (more to read through, plus you have to figure out which comments go with which issue)
• generates less interest (users may have looked at the original question and decided they can’t help with it, and so ignore the thread from then on).

In the future, just make a new thread. That’s what they’re for.

i’m sure some improvements can be made to make it more reliable/readable.

1. I strongly recommend you do not use the “alternate” block syntax (the foo…endfoo syntax). It is not very readable or maintainable, and can lead to lots of mistakes when you work on the code in the future. The standard form is what you should be using.

while(){
    /* do stuff */
}

…I think the alternate form only survives because it’s so widely used in WordPress.

1. I try to keep all of my business logic separate from the output (display) code. This is good for both readability and resilience: for example, if you encounter an error when fetching you database records, you’ll be stuck with a broken html <table> and no way to redirect or show an error page instead.

$q = connect->query( $sql );
$rows = $q->fetchAll( PDO::FETCH_ASSOC );

//  . . .

?>
<!--  your html markup  -->
<?php
foreach( $rows as $row ){
    echo '<img src="'.$row['COVER_URL'].'" alt="'.$row['TITLE'].'"';
    //  etc., etc.  . . .
}
?>

This should work just fine, so long as you don’t have tons and tons of result rows.

1. In your catch block, $e->getMessage() is a no-op. You don’t display or log the message, nor make any decisions based on its value, so there’s no reason to access the message at all.

---

All in all, however, doesn’t look bad.

BenWalker

# August 20, 2014 at 2:43 pm

Hope you don’t mind me subverting the thread slightly, but I’m curious:

I strongly recommend you do not use the “alternate” block syntax (the foo…endfoo syntax). It is not very readable or maintainable

I use the syntax pretty much as the OP does – i.e. only in my templates to make it consistent with the HTML alongside – and find the opposite (more readable, easier to maintain). Is your comment above just a personal preference, or would you advocate switching regardless?