current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

EDIT: CODE CHANGED AND QUESTION UPDATED FOR LATEST ERROR.

I need to populate a dropdown list of book titles from my postgreSQL database using a query such as SELECT title FROM books WHERE ownedBy = [users facebook ID] and then use the selection by the user to display the rest of the information on that book. The page is a facebook app, which is how I'm getting the facebook ID.

This is the relavent section of code so far, mostly created from various answers to similar questions I have found.

<form action="updateform.php" method="post">
  <input type="hidden" name="userid" id="userid" value="<?php echo htmlspecialchars($user_id); ?>"
  <select name="booktitle" id="booktitle">
  <option>Select book</option>
  <?php
    $db = pg_connect("host=ec2-54-243-190-226.compute-1.amazonaws.com port=5432 dbname=d6fh4g6l0l6gvb user=zmqygfamcyvhsb password=1Apld4ivMXSK8JZ_8yL7FwIuuz sslmode=require options='--client_encoding=UTF8'") or die('Could not connect: ' . pg_last_error());
    $sql = pg_query("SELECT title FROM books WHERE ownedby='$user_id'";
    while ($row = pg_fetch_assoc($sql)) {
    echo '<option value="'.htmlspecialchars($row['title']).'"></option>';}
    pg_close($db);
    ?>
    </select>
//other form elements here
</form>

Currently, no drop down box is displaying, and the server log says pg_query() expects parameter 1 to be resource, string given in /app/www/update.php on line 275 and the same error for pg_fetch_assoc

If anyone can help me get the drop down box part to work, that would be great for now, I'll work on figuring the rest out myself once this part is working.

share|improve this question
add comment

4 Answers

up vote 1 down vote

Unescaped quotes inside quotes

This

$sql = pg_query("SELECT title FROM books WHERE ownedby=("$user_id")");

Should be

$sql = pg_query("SELECT title FROM books WHERE ownedby='$user_id'");

Or

$sql = pg_query("SELECT title FROM books WHERE ownedby=\"$user_id\"");
share|improve this answer
add comment
up vote 1 down vote

try this:

<form action="updateform.php" method="post">
<select name="bookTitle">
  <?php
        $db = pg_connect("host=ec2-54-243-190-226.compute-1.amazonaws.com port=5432 dbname=d6fh4g6l0l6gvb user=zmqygfamcyvhsb password=[removed] sslmode=require options='--client_encoding=UTF8'") or die('Could not connect: ' . pg_last_error());
        $sql = pg_query(sprintf("SELECT title FROM books WHERE ownedby=%d", $user_id));
        while ($row = pg_fetch_assoc($sql)) {
            echo '<option value="'.htmlspecialchars($row['title']).'"></option>';
        }
        pg_close($db);
  ?>
</select>

Note the use of 'echo' instead of closing the PHP tags.

Also, you should probably use an ID, rather than a title for the option value :)

Regards, Phil

share|improve this answer
    
Trying that now, I'd picked up the closing php tags from another solution I'd found, I did find them rather confusing! –  TheMarron Mar 27 '13 at 17:29
    
Using that code I no longer get a 500 error, but I also don't get a drop down box. –  TheMarron Mar 27 '13 at 17:38
    
Have you got a link to your live website where I can take a look? –  Modul8 Mar 27 '13 at 17:41
    
boiling-plains-5838.herokuapp.com/update.php - excuse the rather odd name, heroku automatically generates some rather strange ones. –  TheMarron Mar 27 '13 at 17:43
    
Hmm....it doesn't appear that you get your form either....can you paste the full source code of that page at pastie.org ? –  Modul8 Mar 28 '13 at 14:14
add comment
up vote 1 down vote

The line 

$sql = pg_query("SELECT title FROM books WHERE ownedby=("$user_id")");

contains an error because the double quotes before $user_id closes the string "SELECT .... A quick fix is to change "$user_id" with '$user_id' and remove the braces ()

In general it is not a good practice to directly put variables in SQL queries because your code becomes vulnerable to SQL Injection. Consider using prepare, bind and execute statements.

share|improve this answer
add comment
up vote 0 down vote

You haven't escaped the quotes on Line 275. It should be

$sql = pg_query("SELECT title FROM books WHERE ownedby=\"$user_id\"");

or

$sql = pg_query('SELECT title FROM books WHERE ownedby="'.$user_id.'"');
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.