[Resolved] Data retrieval via user input form

fakename

I'm trying to retrieve data through a form that allows user input. The script below works on the command line but I cant seem to execute via a form.

select credit.ExternalID
from credit,invoice
where credit.Purchaser = invoice.User
and invoice.ExternalID = 24607;

Here is what I've done so far, with no luck, to try to accomplish this'

<?php
/ Connecting, selecting database /
$link = mysql_connect(" ", "dbname", "")
or die("Could not connect : " . mysql_error());

mysql_select_db("dbname", $link) or die("Could not select database");

$searchterm=$http_post_vars['searchterm'];

/ Performing SQL query /
$query = "select credit.ExternalID" ;
$query .="from credit,invoice";
$query .="where credit.Purchaser = invoice.User";
$query .="and invoice.ExternalID = '" .$searchterm ."'";
$result = mysql_query($query,$link) or die("Query failed : " .
mysql_error());

print " " . $result . " ";

/ Closing connection /
mysql_close($link);

Any help appreciated.

bradgrafelman

Do you get any errors? Is reporting set to "E_ALL" ?

Try replacing this line:

$searchterm=$http_post_vars['searchterm'];

with this:

$searchterm = (($_POST['searchterm']) ? $_POST['searchterm'] : '');

Other than that, turn on reporting to E_ALL and post any errors/warnings/notices you get. Also, a link tot his script would probably help too.

fakename

Both the exsisting code and your changes give me the same error:

Query failed : 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 '.Purchaser = invoice.Userand invoice.ExternalID = '''

I appreciate the response though. Thanks

bradgrafelman

D'oh! That's it.

You need a space between the 'and' in your query and the preceding word ".User".

Any reason you split this variable into multiple lines? Readability? If so, you could simply concatenate it using periods:

$query = "select credit.ExternalID"
       . " from credit,invoice"
       . " where credit.Purchaser = invoice.User"
       . " and invoice.ExternalID = '" .$searchterm ."'";

NOTE: In my above example, I also fixed the missing space before the 'and' in your query.

tekky

/* Performing SQL query */ 
$query = "select credit.ExternalID" ; 
$query .="from credit,invoice"; 
$query .="where credit.Purchaser = invoice.User"; 
$query .="and invoice.ExternalID = '" .$searchterm ."'"; 
$result = mysql_query($query,$link) or die("Query failed : " . 
mysql_error());

when concatenating you need to manually INCLUDE the space between words

tekky

You are still injecting errors into his query 😉

$string = "blah blah"; . "blah blah"; // wont work either ;)

Originally posted by bradgrafelman
D'oh! That's it.

You need a space between the 'and' in your query and the preceding word ".User".

Any reason you split this variable into multiple lines? Readability? If so, you could simply concatenate it using periods:
$query = "select credit.ExternalID" ; 
                 . "from credit,invoice"
                 . "where credit.Purchaser = invoice.User"
                 . " and invoice.ExternalID = '" .$searchterm ."'"; 
NOTE: In my above example, I also fixed the missing space before the 'and' in your query. [/B]

bradgrafelman

Bah! You caught me while editing! lol...

Was editing spacing issues, and also noticed that unwanted semicolon. 'Tis gone now, and should work as expected.

EDIT: For clarity's sake, here's the correct code again (after all edits):

$query = "select credit.ExternalID"
       . " from credit,invoice"
       . " where credit.Purchaser = invoice.User"
       . " and invoice.ExternalID = '" .$searchterm ."'";

EDIT2: Also, as a good debugging practice, always echo your $query if you're finding it's erroring. That way, we can look at the exact query PHP is sending to your MySQL server, rather than try to decrypt the code.

fakename

With the revised code that included the new spacing, I continued to get a similar syntax error/ I added more spaces(see below and got a new error:

Resource id #3

$searchterm = (($POST['searchterm']) ? $POST['searchterm'] : '');

/ Performing SQL query /
$query = "select credit.ExternalID "
. "from credit,invoice "
. "where credit.Purchaser = invoice.User "
. " and invoice.ExternalID = '" .$searchterm ."'";

$result = mysql_query($query,$link) or die("Query failed : " . mysql_error());

print " " . $result . " ";

bradgrafelman

'Resource id #3' is not an error; its a MySQL resource. To see your query, output $query, not $result.

What's the error? Doesn't look like you're getting one. Is something still not working properly?

fakename

I guess I'm not writting the output correctly
with this line :
print " " . $query . " ";

I get this output:

select credit.ExternalID from credit,invoice where credit.Purchaser = invoice.User and invoice.ExternalID = '24607'

tekky

add this after your mysql_result()

while($row = mysql_fetch_assoc($result))
  foreach($row as $key=>$val)
    echo $key . '=' . $val . '<br>';

fakename

That was the final piece! Tahnk you all. I love this forum.

bradgrafelman

That's what we're here for.

Glad you got your issue resolved. Don't forget to MARK THIS THREAD RESOLVED using the link at the bottom of the page.