Logic Error With Dynamic Page Display:

[elite_prodigy]

I'm not getting expected results.

 

I'm using the following code to determine if a site even exists in the database:

 

<?php

$sql = "SELECT * FROM `sites` WHERE `site`='$site';";

if ($result = mysql_query($sql,$conn)) {
  if (mysql_num_rows($result)) {
    if ($_SESSION['count'] < 1){
      $page = "home";
      $_SESSION['count'] = $_SESSION['count']++;
    }
  }
} else {
  $page = "sign";
}

?>

 

The above tests to see if a query can be executed on a particular table, then if the record in the table exists, then decides if this is their first visit of the day and if it is sets the $page variable to "home" which will be used later in my switch structure.

 

If a query cannot be executed on the database, then the record isn't there which setes the $page variable to "sign" which tells the switch structure to display a page telling the user that the site does not exist and that it may be registered for free.

 

The only page that keeps appearing is the "Sign Up" page, even when records exist in the database:

http://www.exposemyschool.com/images/scrnShot_phpMyAdmin.jpg

 

Try visiting one of the following sites:

 

http://www.exposemyschool.com/fun

http://www.exposemyschool.com/test

 

Both return the Sign Up Page.

 

Does anyone know why this is happening?

 

Here is the full functions.php file. It may help to understand my problem and where I'm going with the whole thing:

 

<?php


session_start();
include('config.php');

$site = $_SESSION['site'];
$page = $_SESSION['page'];


$sql = "SELECT * FROM `sites` WHERE `site`='$site';";

if ($result = mysql_query($sql,$conn)) {
  if (mysql_num_rows($result)) {
    if ($_SESSION['count'] < 1){
      $page = "home";
      $_SESSION['count'] = $_SESSION['count']++;
    }
  }
} else {
  $page = "sign";
}

switch($page){


case "home":{

  $sql = "SELECT `content` FROM `home` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn);

  $area2 = $query;

  $sql = "SELECT * FROM `home_alerts` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn) or die(mysql_error());

  while($notice = mysql_fetch_array($query)){

   $title = $notice['title'];
   $content = $notice['content'];
   $author = $notice['added_by'];
   $time = $notice['added_on'];

   $area1 .= '<div class="alert"><h1 class="alert_title">{$title}</h1><div class="alert_content">{$content}</div><div class="author">Placed By: {$author}</div> <div class="date">On: {$time}</div></div>';

  } 

  break;
}

case "secret":{

  $sql = "SELECT * FROM `posts` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){

   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';
}

   break;

}

case "digger":{

  $area2 = '
<div class="form">
<form method="post" action="contribute.php">

<div class="prompt_no_break">Alias:</div><div class="input"><input type="text" name="alias" /></div>
<div class="prompt_no_break">Title:</div><div class="input"><input type="text" name="title" width="100" /></div>
<div class="prompt_break">Contribution:</div><div class="input"><textarea rows="25" cols="50" wrap="virtual"></textarea></div>
</div>
';
   break;

}

case "archive":{

  $sql = "SELECT * FROM `dirt_approve` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){


   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';

   break;
   
}
}

case "staff":{

  $sql = "SELECT `alias` FROM `staff` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($staff = mysql_fetch_array($query)){

   $alias = $staff['alias'];

   $alias_list .= '<li>{$alias}</li>';

  }

  $area2 = '<div class="staff"><ul>{$alias_list}</ul></div>';

   break;

}

case "sign":{

$area2 = '<div class="available"><h1>Congratulations!</h1>This domain is available for free registration.<a href="http://www.exposemyschool.com/sign_up.php">Sign Up Now!</a></div>';	

break;

}

default:{

  $area2 = '<div class="nav_error"><h1 class="title">Oops!</h1>The following error(s) occured while attempting to process your request:
<ul>
<li>The page you are looking for does not exist</li>
<li>The page was moved</li>
<li>The site you are searching for no longer exists</li>
<li>The site you are attempting to access has been deleted due to a terms of service violation.</li>
</ul>
Possible Solutions: Contact technical-support [at] exposemyschool [dot] com . There may be a fatal systems error that needs attention. Try refreshing the page. Make sure you typed the address correctly. If you are the owner of this site and are unable to access it contact User Services at: user-service [at] exposemyschool [dot] com . If you recently registered this site then try waiting. It may take several minutes to register in our systems.</div>';

   break;
}
}


?>

 

(please be patient with all above links, scripts are being run on the server which cause Apache to restart every few minutes)

[BlueSkyIS]

mysql_query($sql) or die(mysql_error())

[elite_prodigy]

Unknown column 'test' in 'where clause'

 

I forgot to post that error, with my page.

[gizmola]

Well multiple comments:

 

1. Don't need a semi colon in your sql statement. The mysql functions already know how to submit a statement.

2. Round about way to get this.  Much better to do Select count(*) as countof FROM table.  This will *always* return a valid result set.  You're only interested of course if there's more than one row, so simply check the return value for > 0.

 

3.  Your problem is that to specify a literal in mysql, you use double quotes not single quotes.  Several different ways to do that, but easy one is escape the double quotes, since you're trying to interpolate in your variables.

 

$sql = "SELECT * FROM `sites` WHERE `site`=\"$site\"";

 

Not as readable as using concatenation for most people.

 

$sql = 'SELECT * FROM `sites` WHERE `site`= "' . $site . '"';

[elite_prodigy]

Unknown column 'test' in 'where clause'

 

Same error being generated.

 

 

...

 

2. Round about way to get this.  Much better to do Select count(*) as countof FROM table.  This will *always* return a valid result set.  You're only interested of course if there's more than one row, so simply check the return value for > 0.

 

...

 

 

I have no idea what you're talking about there, could you explain it or write an example?

 

Does anyone know how to solve the error I'm getting? And why the proper page isn't being displayed?

 

 

[elite_prodigy]

Anyone?

Please, I'm desperate to solve this error.

[gizmola]

Post the exact code you are trying to use please.

[sasa]

error means that sql string 'test' use as field name `test`

 

[revraz]

Show us your Table layout and field names. 

[elite_prodigy]

<?php


session_start();
include('config.php');

$site = $_SESSION['site'];
$page = $_SESSION['page'];


$sql = "SELECT * FROM `sites` WHERE `site`='$site';";

if ($result = mysql_query($sql,$conn)) {
  if (mysql_num_rows($result)) {
    if ($_SESSION['count'] < 1){
      $page = "home";
      $_SESSION['count'] = $_SESSION['count']++;
    }
  }
} else {
  $page = "sign";
}

switch($page){


case "home":{

  $sql = "SELECT `content` FROM `home` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn);

  $area2 = $query;

  $sql = "SELECT * FROM `home_alerts` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn) or die(mysql_error());

  while($notice = mysql_fetch_array($query)){

   $title = $notice['title'];
   $content = $notice['content'];
   $author = $notice['added_by'];
   $time = $notice['added_on'];

   $area1 .= '<div class="alert"><h1 class="alert_title">{$title}</h1><div class="alert_content">{$content}</div><div class="author">Placed By: {$author}</div> <div class="date">On: {$time}</div></div>';

  } 

  break;
}

case "secret":{

  $sql = "SELECT * FROM `posts` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){

   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';
}

   break;

}

case "digger":{

  $area2 = '
<div class="form">
<form method="post" action="contribute.php">

<div class="prompt_no_break">Alias:</div><div class="input"><input type="text" name="alias" /></div>
<div class="prompt_no_break">Title:</div><div class="input"><input type="text" name="title" width="100" /></div>
<div class="prompt_break">Contribution:</div><div class="input"><textarea rows="25" cols="50" wrap="virtual"></textarea></div>
</div>
';
   break;

}

case "archive":{

  $sql = "SELECT * FROM `dirt_approve` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){


   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';

   break;
   
}
}

case "staff":{

  $sql = "SELECT `alias` FROM `staff` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($staff = mysql_fetch_array($query)){

   $alias = $staff['alias'];

   $alias_list .= '<li>{$alias}</li>';

  }

  $area2 = '<div class="staff"><ul>{$alias_list}</ul></div>';

   break;

}

case "sign":{

$area2 = '<div class="available"><h1>Congratulations!</h1>This domain is available for free registration.<a href="http://www.exposemyschool.com/sign_up.php">Sign Up Now!</a></div>';	

break;

}

default:{

  $area2 = '<div class="nav_error"><h1 class="title">Oops!</h1>The following error(s) occured while attempting to process your request:
<ul>
<li>The page you are looking for does not exist</li>
<li>The page was moved</li>
<li>The site you are searching for no longer exists</li>
<li>The site you are attempting to access has been deleted due to a terms of service violation.</li>
</ul>
Possible Solutions: Contact technical-support [at] exposemyschool [dot] com . There may be a fatal systems error that needs attention. Try refreshing the page. Make sure you typed the address correctly. If you are the owner of this site and are unable to access it contact User Services at: user-service [at] exposemyschool [dot] com . If you recently registered this site then try waiting. It may take several minutes to register in our systems.</div>';

   break;
}
}


?>

 

That is the full functions.php file where the error is occuring. I'll get screenshots up later.

[sasa]

change

WHERE `site`=".$site.";";

to

WHERE `site`='".$site."';";

ad '

[minidak03]

change

WHERE `site`=".$site.";";

to

WHERE `site`='".$site."';";

ad '

 

Correct or do this

 

$sql = "SELECT * FROM `sites` WHERE site = '$site'";

[elite_prodigy]

Okay, now I'm getting the "Unknown column 'test' in 'where clause' " error when I enter a site name that IS in the database

 

[elite_prodigy]

Okay, I've dumped the DB into this text file:

http://www.exposeyourschool.com/downloads/FREE_HOST_TABLES.txt

 

And instead of getting the "This URL is free for registration" message when a sub-directory is entered that isn't in the DB (see case "sign") I get the error message (see case "default").

 

I'm sure this is something simple with the way I'm handling variables and what not, but I can't figure this out and it doesn't help that I'm working five days a week without a reliable Internet connection.

 

 

<?php


session_start();
include('config.php');

$site = $_SESSION['site'];
$page = $_SESSION['page'];


$sql = "SELECT * FROM `sites` WHERE `site`='$site';";

if ($result = mysql_query($sql,$conn)) {
  if (mysql_num_rows($result)) {
    if ($_SESSION['count'] < 1){
      $page = "home";
      $_SESSION['count'] = $_SESSION['count']++;
    }
  }
} else {
  $page = "sign";
}

switch($page){


case "home":{

  $sql = "SELECT `content` FROM `home` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn);

  $area2 = $query;

  $sql = "SELECT * FROM `home_alerts` WHERE `site`=".$site.";";
  $query = mysql_query($sql,$conn) or die(mysql_error());

  while($notice = mysql_fetch_array($query)){

   $title = $notice['title'];
   $content = $notice['content'];
   $author = $notice['added_by'];
   $time = $notice['added_on'];

   $area1 .= '<div class="alert"><h1 class="alert_title">{$title}</h1><div class="alert_content">{$content}</div><div class="author">Placed By: {$author}</div> <div class="date">On: {$time}</div></div>';

  } 

  break;
}

case "secret":{

  $sql = "SELECT * FROM `posts` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){

   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';
}

   break;

}

case "digger":{

  $area2 = '
<div class="form">
<form method="post" action="contribute.php">

<div class="prompt_no_break">Alias:</div><div class="input"><input type="text" name="alias" /></div>
<div class="prompt_no_break">Title:</div><div class="input"><input type="text" name="title" width="100" /></div>
<div class="prompt_break">Contribution:</div><div class="input"><textarea rows="25" cols="50" wrap="virtual"></textarea></div>
</div>
';
   break;

}

case "archive":{

  $sql = "SELECT * FROM `dirt_approve` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($secret = mysql_fetch_array($query)){


   $title = $secret['title'];
   $alias = $secret['alias'];
   $content = $secret['content'];
   
   $area2 .= '<div class="secret"><h1 class="title">{$title}</h1>{$content}<div class="author">Author: {$author}</div></div>';

   break;
   
}
}

case "staff":{

  $sql = "SELECT `alias` FROM `staff` WHERE `site`=".site.";";
  $query = $mysql_querry($sql,$conn);

  while($staff = mysql_fetch_array($query)){

   $alias = $staff['alias'];

   $alias_list .= '<li>{$alias}</li>';

  }

  $area2 = '<div class="staff"><ul>{$alias_list}</ul></div>';

   break;

}

case "sign":{

$area2 = '<div class="available"><h1>Congratulations!</h1>This domain is available for free registration.<a href="http://www.exposemyschool.com/sign_up.php">Sign Up Now!</a></div>';	

break;

}

default:{

  $area2 = '<div class="nav_error"><h1 class="title">Oops!</h1>The following error(s) occured while attempting to process your request:
<ul>
<li>The page you are looking for does not exist</li>
<li>The page was moved</li>
<li>The site you are searching for no longer exists</li>
<li>The site you are attempting to access has been deleted due to a terms of service violation.</li>
</ul>
Possible Solutions: Contact technical-support [at] exposemyschool [dot] com . There may be a fatal systems error that needs attention. Try refreshing the page. Make sure you typed the address correctly. If you are the owner of this site and are unable to access it contact User Services at: user-service [at] exposemyschool [dot] com . If you recently registered this site then try waiting. It may take several minutes to register in our systems.</div>';

   break;
}
}


?>