1 Topic by Peter (edited by Peter 2007-03-16 16:50)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Topic: PHP/MySQL syntax problems

I had this leftover from a previous login script:

$query = "SELECT * FROM users WHERE username = '" . $pun_user['username'] . "' ";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);

It doesn't work anymore now that I've brought the page into PunBB.

How does PunBB connect to the database to get some data about the user that's logged in? I couldn't find good examples within the code. I'm guessing it should be something like this:

$result = $db->query('SELECT * FROM users WHERE username = $pun_user['username']') or error('Database error', $db->error());
$row = mysql_fetch_array($result);

But I can't even get the syntax right. Keep getting errors. Don't know how to find the answer using Google either.

Can anyone help? sad

Peter's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

Something like that! You forgot the db->prefix

$result = $db->query('SELECT id, username FROM '.$db->prefix.'users ORDER BY id DESC') or error('Unable to fetch user data', __FILE__, __LINE__, $db->error());
$var = $db->result($result);
FluxBB - v1.4.8

lie2815's Website

3 Reply by Peter (edited by Peter 2007-03-16 01:42)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

Thanks lie2815! That looks nothing like what I had, so I would never have guessed that.

It's not pulling up the data I needed though. Below it I had this code:

First Name:<input type="text" class="formfield" name="registrant_firstname" value="<? echo $row['firstname']; ?>" /><br />
Last name:<input type="text" class="formfield" name="registrant_lastname" value="<? echo $row['surname']; ?>" />

As it is now the query code produces only the number 4 in the form fields; that's the ID.

I'll try to tweak the query and report back with the results...

edit:

I still need to figure out the correct way to put this query in there:

SELECT * FROM users WHERE username = $pun_user['username']

That query is probably wrong, but I need -> various data from the user that's currently logged in.

Where should I put the . and " and ) etc. especially around $pun_user['username']?

Peter's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

Could you post some more code?
What exactly do you want to do?

FluxBB - v1.4.8

lie2815's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

replace
$var = $db->result($result);
with
$row = $db->fetch_assoc($result);
and change the query to grab the information

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

Oops, yeah, I should have asked what exactly Peter wanted roll

FluxBB - v1.4.8

lie2815's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

$result = $db->query('SELECT * FROM '.$db->prefix.'users WHERE id = '.$pun_user['id']) or error('Unable to fetch user data', __FILE__, __LINE__, $db->error());

8 Reply by lie2815 (edited by lie2815 2007-03-16 01:48)

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

$result = $db->query('SELECT id, username,...  FROM '.$db->prefix.'users WHERE id = '.$pun_user['id']) or error('Unable to fetch user data', __FILE__, __LINE__, $db->error());

Replace the ... with everything you need.

EDIT: Oops, I guess, Smartys was faster.

FluxBB - v1.4.8

lie2815's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

By the way, Smartys forgot the FROM

FluxBB - v1.4.8

lie2815's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

You might have to put all the row names instead of *

FluxBB - v1.4.8

lie2815's Website

11 Reply by Peter (edited by Peter 2007-03-16 21:17)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

This works!

$result = $db->query('SELECT * FROM '.$db->prefix.'users WHERE id = '.$pun_user['id']) or error('Unable to fetch user data', __FILE__, __LINE__, $db->error());
$row = $db->fetch_assoc($result);

Thanks lie2815 and Smartys!!! smile

Peter's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

No problem. Just out of interest: Could I see your website, pleaaaaase?

FluxBB - v1.4.8

lie2815's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

Just by the way, what were you trying to do anyways?

FluxBB - v1.4.8

lie2815's Website

14 Reply by Peter (edited by Peter 2007-03-16 03:11)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

I'm trying to integrate an event script. When logged-in member registers for an event his name and email are already on the form. Prevents people from registering with different names and email addresses as well.

This general principle will probably be usefull at other places within the site.

Peter's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

I like what you did to your front page. Did you use the PunPortal mod to do the news and stuff?
I have a lot of other blocks there. So, if you need any other code for other blocks (like a nice whosonline block), just ask me... lol
Oh, but it looks like you're not working with blocks anyways.
Never mind wink

FluxBB - v1.4.8

lie2815's Website

16 Reply by Peter (edited by Peter 2007-03-16 04:16)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

Another unrelated php syntax problem... sad

An error was encountered
File: /home/website/public_html/forum/edit.php
Line: 28

PunBB reported: Unable to fetch post info

Database reported: 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 'ORDER BY posted LIMIT 1' at line 1 (Errno: 1064)

Here's line 28 from edit.php:

$result = $db->query('SELECT id FROM '.$db->prefix.'posts WHERE topic_id='.$cur_post['tid'].' ORDER BY posted LIMIT 1') or error('Unable to fetch post info', __FILE__, __LINE__, $db->error());

Can anyone spot the syntax error in that line?

I also had the division by zero error and that fix didn't work. Posts don't show up. They are in the database. The syntax error in that line must be the cause.

Peter's Website

  • From: Germany
  • Registered: 2007-02-14
  • Posts: 644

Re: PHP/MySQL syntax problems

I guess, but I don't know. I guess there is something wrong with the ORDER BY statement

FluxBB - v1.4.8

lie2815's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

Did you edit the query above it that grabs the information?

19 Reply by Peter (edited by Peter 2007-03-16 17:12)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

Smartys wrote:

Did you edit the query above it that grabs the information?

Not that I know. I'll try replacing it with original code. Maybe I messed up with a word replace action. I'll report back...

edit: No, unfortunately no change. I didn't edit anything in the code.

Peter's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

OK, enable PUN_SHOW_QUERIES and paste the error here

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

Looks like you did it
http://bizpartnerlounge.com/forum/edit.php
Looks like you also edited away some code, since that should be giving me a Bad request message

22 Reply by Peter

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

Thanks! Here it is:

An error was encountered
File: /home/mysite/public_html/forum/edit.php
Line: 28

PunBB reported: Unable to fetch post info

Database reported: 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 'ORDER BY posted' at line 1 (Errno: 1064)

Failed query: SELECT id FROM posts WHERE topic_id= ORDER BY posted

Peter's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

Undo the changes you've done wink

24 Reply by Peter (edited by Peter 2007-03-16 17:36)

  • Peter
  • Senior Member
  • Offline
  • From: Brooklyn, NY
  • Registered: 2006-01-10
  • Posts: 205

Re: PHP/MySQL syntax problems

Smartys wrote:

Undo the changes you've done wink

That's my point, I'm not aware I made any changes to this code and to be sure I've put the original code back in. Everything else works. I know PunBB code is sacred and I'm not supposed to touch it, but I'm not going to reverse all my integration work and start from scratch with a fresh install.

There must be a reason why the code can't find the posts. That's what I want to focus on solving.

Peter's Website

  • Smartys
  • Former PunBB Developer
  • Offline
  • Registered: 2004-04-30
  • Posts: 7,844

Re: PHP/MySQL syntax problems

Mmm, if I register an account, could you give me admin and let me see if I can fix at least your other issue?
And I wasn't asking you not to edit PunBB's code or to undo what you had done, I was asking you to try undoing whatever you had done to edit.php so that it would have the correct behavior