Page 1 of 1

select where X = XX AND Y = YY ?

Posted: Fri Jun 20, 2003 7:59 pm
by Caroline
Here is the table structure

Code: Select all

TABLE my_users (
  id int(11) NOT NULL auto_increment,
  username varchar(50) NOT NULL default '',
  displayname varchar(50) NOT NULL default '',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
);

TABLE my_docs (
  id int(11) NOT NULL auto_increment,
  grp varchar(18) NOT NULL default '',
  title varchar(171) NOT NULL default '',
  intro text NOT NULL,
  doc mediumtext NOT NULL,
  active tinyint(4) NOT NULL default '0',
  author varchar(45) NOT NULL default '',
  date int(11) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
);
// grp is article group as PHP, HTML, JavaScript
// active is 1 (yes/display) or 0 (no/pending)

As I want to show visitors only active articles in PHP group, I did the following

Code: Select all

$doc_list = mysql_query ("select id,grp,title,intro,active,author,date from my_docs where grp = PHP and active = 1 order by id desc limit 5");
while ($doc = mysql_fetch_array($doc_list)) {
$postdate = date("m.d.Y",$doc[date]);
$time = date("H:i",$doc[date]);
$quser = mysql_query ("select id,username,displayname from my_users where username = '$doc[author]'");
$user = mysql_fetch_array ($quser);
echo "
<p>
<a href='?view=$doc[id]'><b>$doc[title]</b></a><br>
Author: <b>$user[displayname]</b> :: Posted $time, $postdate
<p>$doc[intro]
";
In fact, the code prints nothing to the screen. What's wrong?

A guy in EFNET has told me to put PHP in quotes (Like this grp = 'PHP'). I did, but it didn't help.

Thanks for reading :wink:

Posted: Fri Jun 20, 2003 8:43 pm
by phice
erm, order your SQL statements properly...

first mysql_query()
=========================================
SELECT id,grp,title,intro,active,author,date
____FROM `my_docs`
____WHERE `grp` = 'PHP'
________AND `active` = '1'
____ORDER BY id DESC
____LIMIT 0,5
=========================================


second mysql_query()
=========================================
SELECT id,username,displayname
____FROM `my_users`
____WHERE `username` = '$doc[author]'
=========================================



Note: I placed underlines (_) to denote tabs for each row of the query. Remove them to allow your query to work properly. Also, when SELECTing a certain column in which it's contents are alphanumeric, use single quotes (') around them.


Hope this helps.

Posted: Sat Jun 21, 2003 8:28 am
by delorian
I've rewritten your code:

Code: Select all

$doc_list = mysql_query ("select id,grp,title,intro,active,author,date from my_docs where grp='PHP' and active=1 order by id desc limit 5"); 

while ($doc = mysql_fetch_array($doc_list)) { 

  $postdate = date("m.d.Y",$doc['date']); 
  $time = date("H:i",$doc['date']); 
  $quser = mysql_query ("select id,username,displayname from my_users where username='".$doc['author']."'"); 
  $user = mysql_fetch_array ($quser); 

  echo "<p>";
  echo "<a href='?view=".$doc['id']."'>";
  echo "<b>".$doc['title']."</b></a><br>";
  echo "Author: <b>".$user['displayname']."</b>";
  echo ":: Posted ".$time.", ".$postdate."<p>".$doc['intro'];

Worked!

Posted: Sat Jun 21, 2003 4:52 pm
by Caroline
Image It's worked!

Thanks, my gurus!

Enjoy helping!