select where X = XX AND Y = YY ?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

select where X = XX AND Y = YY ?

Post 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:
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post 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.
Image Image
User avatar
delorian
Forum Contributor
Posts: 223
Joined: Sun May 04, 2003 5:20 pm
Location: Olsztyn, Poland

Post 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'];
Caroline
Forum Commoner
Posts: 25
Joined: Thu Jan 30, 2003 1:38 pm

Worked!

Post by Caroline »

Image It's worked!

Thanks, my gurus!

Enjoy helping!
Post Reply