Something wrong in this line

PHP
1

I made a site a while ago for my daughters dance school.
On one page all the different styles are supposed to be listed from a MySQL database.
In the admin page she can pick 1 if a style should be listed and 0 if it shouldn’t be visible.
But when she mark it with a zero it is still showing.
What is wrong in my code here?

I made it all in Dreamweaver.
The part where she can change is called “dance_dans.listas”

"SELECT dance_kurs.dans, dance_kurs.kursnr, dance_dans.listas FROM dance_dans, dance_kurs WHERE substr(dance_kurs.kursnr, 12, 1) = '$lokal' AND dance_dans.listas = '1' GROUP BY dance_kurs.dans ASC"
2

Wow! Thanks a lot. That worked!!!

3

so the tables are related by VARCHAR columns? this could be trouble


SELECT dance_kurs.dans
     , dance_kurs.kursnr
     , dance_dans.listas 
  [COLOR="Blue"]FROM dance_dans
INNER
  JOIN dance_kurs 
    ON dance_kurs.dans = dance_dans.dans[/COLOR]
 WHERE substr(dance_kurs.kursnr,12,1) = '$lokal' 
   AND dance_dans.listas = '1' 
GROUP 
    BY dance_kurs.dans ASC
4

yes, like that, although unfortunately it did not help me to understand the tables

what are those tables used for? what’s in them? how are the tables related?

5

Well the large table is the classes for the dance school.
The id, the number, year, term, type of dance (the related one), age group, level, courses, price, teacher, room, starts, weekday, time, active

The other table contains
The id, type of dance (the related one), comments, listed or not

6

Do you mean like this?

CREATE TABLE `dance_kurs` (
  `kurs_id` int(2) NOT NULL auto_increment,
  `kursnr` varchar(20) default NULL,
  `kursar` mediumint(4) NOT NULL default '0',
  `termin` char(2) NOT NULL default '',
  `dans` varchar(255) default NULL,
  `age` varchar(20) NOT NULL default '',
  `level` varchar(20) NOT NULL default '',
  `ggr` mediumint(2) NOT NULL default '0',
  `pris` mediumint(4) NOT NULL default '0',
  `ledare` varchar(30) NOT NULL default '',
  `lokal` varchar(40) NOT NULL default '',
  `startar` date NOT NULL default '0000-00-00',
  `veckodag` varchar(15) NOT NULL default '',
  `klockan` varchar(15) NOT NULL default '',
  `aktiv` smallint(1) NOT NULL default '1',
  PRIMARY KEY  (`kurs_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE `dance_dans` (
  `dans_id` int(2) NOT NULL auto_increment,
  `dans` varchar(30) default NULL,
  `beskr` longtext NOT NULL,
  `listas` char(2) NOT NULL default '1',
  PRIMARY KEY  (`dans_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;
7

find the columns in each table that need to be equal for the joined rows

typically this will be the primary in one table and a foreign key in the other

if you would do a SHOW CREATE TABLE for both tables in mysql, i might be able to guess

8

And how do I do that?

9

only a little :slight_smile:

to start with, it’s really really difficult to understand a query that’s all strung out on a single line – please learn to use line breaks and indents

SELECT dance_kurs.dans
     , dance_kurs.kursnr
     , dance_dans.listas 
  FROM dance_dans
     , dance_kurs 
 WHERE substr(dance_kurs.kursnr,12,1) = '$lokal' 
   AND dance_dans.listas = '1' 
GROUP 
    BY dance_kurs.dans ASC

ah, that’s better :slight_smile:

okay, the problem is, you haven’t joined the tables properly, and you’re getting cross join effects

each of the $lokal dans_kurs rows is matched with every listas=1 row in the other table