SQL help

[Swan]

Code: Select all

php
$tablename=watchlist;
$db= naughtylist;
$sql = mysql_connect('localhost', 'root', 'panda');
$db_list = mysql_list_dbs($sql);

while ($row = mysql_fetch_object($db_list)) 
{
     echo $row->Database . "\n";
}



?>

The above is a code to show all of the databases currently stored. I want to be able to check whether or not a particular table with a certain name is to be found within the databases.

I thought that the databases could be assigned into an array with the values then broken down and filtered to check. I am not too sure....can anyone please advise?

[Nerdz]

This should help you

Code: Select all

http://dev.mysql.com/doc/refman/5.0/en/show-tables.html

else, I haven't understand your question..

[Swan]

Ok let me ask a different question then....


can someone please explain to me what a table definition is? Preferably with an example?

[ayu]

Ok let me ask a different question then....


can someone please explain to me what a table definition is? Preferably with an example?

Maybe not a direct answer to your question, but since you got the database listing covered, maybe you could also try this? =)

Code: Select all

http://se2.php.net/manual/en/function.mysql-list-tables.php

[Swan]

Ok...

the big stumbling block I am encountering is conditional statements in SQL, primarily the use of the BEGIN END statements, I cant seem to get them to work.

[Swan]

$username=$_POST['username'];
$offence=$_POST['offence'];
$punishment= $_POST['punishment'];
mysql_connect ('localhost','root','panda');

$res=mysql_query("CREATE DATABASE IF NOT EXISTS naughtylist");
mysql_select_db("naughtylist") or die (mysql_error());
$create=mysql_query("CREATE TABLE IF NOT EXISTS 'watchlist'(
'username' varchar(16) NOT NULL,
'offence' text NOT NULL,
'punishment' text NOT NULL,
PRIMARY KEY ('username)
)");


$query=mysql_query("INSERT INTO watch_list (username, offence, punishment) VALUES ('$username', '$offence', '$punishment'") or die(mysql_error());
echo $query;


Code so far...im getting annoyed becuase the error logs are useless:

"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
" at line 1"

Yeah real helpful

[ayu]

Tip is to use an mySQL client and try the queries there, before you use them in the code. That way you can make sure that the problem is with the code, and not with the query

[Swan]

Code: Select all

$query=mysql_query("CREATE TABLE IF NOT EXISTS watchlist(

username varchar(16) NOT NULL,

offence text NOT NULL,

punishment text NOT NULL,

PRIMARY KEY ('username')

)");

$result=mysql_query($query);


if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

Ive narrowed it down to this segment of the code.

Invalid query: Query was empty Whole query is the error that I get time and time again. I simply havent got the faintest idea where I am going wrong [/quote]

[G-Brain]

Code: Select all

$query=mysql_query("CREATE TABLE IF NOT EXISTS watchlist(

username varchar(16) NOT NULL,

offence text NOT NULL,

punishment text NOT NULL,

PRIMARY KEY ('username')

)");

$result=mysql_query($query);

With $result, you're doing mysql_query on a mysql_query. You're welcome.

[Swan]

Code: Select all

<?php
$selected_db='naughty';

$username=$_POST['username'];

$offence=$_POST['offence'];

$punishment= $_POST['punishment'];

$con=mysql_connect("localhost","root","panda")or die (mysql_error());

include 'error.php';
include 'database.php';

if (!$con)

{



die('could not connect: ' . mysql_error());

}

mysql_select_db("naughtylist") or die(mysql_error());


$query= mysql_query ("CREATE TABLE `watchlist` (
`username` VARCHAR(16) NOT NULL,
`offence` TEXT NOT NULL,
`punishment` TEXT NOT NULL,
PRIMARY KEY (`username`)
")
or die (mysql_error());



if (!$result) {
$message = 'Invalid query: ' . mysql_error() . "\n";
$message .= 'Whole query: ' . $query;
die($message);
}

 

Sorry, I dont know whats wrong with this code

ou have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5

Is all i ever get.

I think I need the " though, else i get a PHP error.....

[bad_brain]

um, shouldn't it be single quotes?

Code: Select all

$con=mysql_connect('localhost', 'root', 'panda')

[Swan]

yeah, makes no diff BB. error still stands

It still seems to be that goddamn whoring CREATE TABLE

[ayu]

yeah, makes no diff BB. error still stands

It still seems to be that goddamn whoring CREATE TABLE

Code: Select all

$query= mysql_query ("CREATE TABLE `watchlist` ( 

you are using the wrong type of quote....

' != `

[Swan]

removing the quotes also has no effect.

http://www.tizag.com/mysqlTutorial/mysqltables.php

The code there doesnt have quotes around the table name, or any of the column values.

The error is 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 '' at line 5.

Now the reason I repeat that is that in the link I just included, that ALSO makes use of the " " quotes, in fact the create table statement is included in full between the " ". As far as I can see,

Code: Select all

mysql_query ("CREATE TABLE watchlist(
username VARCHAR(16) NOT NULL,
offence TEXT NOT NULL,
punishment TEXT NOT NULL,
PRIMARY KEY (username)
")
or die (mysql_error());

my code mimics that code to the letter in terms of form. Removal of the " means that there is a PHP error. Inlcuding the " means there is a SQL error....but the syntax of the mysql_query function is such that the "" is needed. Sorry for being dense....I just really dont get what I am doing wrong..[/quote]

[lars]

removing the quotes also has no effect.

http://www.tizag.com/mysqlTutorial/mysqltables.php

The code there doesnt have quotes around the table name, or any of the column values.

The error is 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 '' at line 5.

Now the reason I repeat that is that in the link I just included, that ALSO makes use of the " " quotes, in fact the create table statement is included in full between the " ". As far as I can see,

Code: Select all

mysql_query ("CREATE TABLE watchlist(
username VARCHAR(16) NOT NULL,
offence TEXT NOT NULL,
punishment TEXT NOT NULL,
PRIMARY KEY (username)
")
or die (mysql_error());

my code mimics that code to the letter in terms of form. Removal of the " means that there is a PHP error. Inlcuding the " means there is a SQL error....but the syntax of the mysql_query function is such that the "" is needed. Sorry for being dense....I just really dont get what I am doing wrong..

Looks like you're missing a closing ) in your SQL statement.

Also, just for good DB design I'd designate a numerical ID column and use "username" as an index...something like:

Code: Select all

CREATE TABLE watchlist(
  id INT NOT NULL AUTO_INCREMENT,
  username NVARCHAR(16) NOT NULL,
  offence TEXT NOT NULL,
  punishment TEXT NOT NULL,
  PRIMARY KEY(id),
  INDEX(username)
) ENGINE=INNODB;

I ran the following in MySQL and it seemed to work just fine.

I usually use InnoDB as the database engine...in my opinion, a database engine without ACID transactions and full relational data (foreign keys etc.) is a sack of shit . To each his own, though...MySQL just integrates so damned well with PHP.

A couple of things....using a backtick (`) should be fine for a table definition in MySQL. Also--and I haven't used this specific MySQL driver in PHP for a while--is it proper to attempt to return a resultset (using $query=...) from a query that returns no rows? It's possible that you're getting an error that isn't really an error on the following line:

Code: Select all

if (!$result) {
    $message = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

because $result is null.

Just a few guesses...hope it helps!