mysql_query hangs?

[GarroteYou]

Hello -

I'm working on some homework here. I can't seem to get my script to send the query to the database, here is the code:

<?php


$db = "movies";				// var for database name
$filename = "Gross.data";	// file with all of the data

if ( file_exists( $filename ) )
{
	$data = file( $filename );
}
else
{
	exit( "The file $filename does not exist\n\n" );
}
							// connect to DB
$link = mysql_connect('localhost', 'movieuser', 'linux');

if ( !$link )
{
	exit( "Could not connect: " . mysql_error( $link ) );
}
else
{
							// use movies as the current db
	$db_selected = mysql_select_db( $db, $link);

	if ( !$db_selected )
	{
		exit( "Cannot use ".$db." : " . mysql_error( $link ) );
	}
	else
	{
							// loop through the .data file adding rows to the table
		foreach ($data as $line_num => $line)
		{
							// array of data
			$items = explode( "|", $line );

							// create query command using above array
$querystring = "INSERT INTO grossRevenue VALUES ( ".$items[0].", ".$items[1].", ".$items[2].", ".$line_num." )";

							// send the command

//				print( $querystring."<br><br>" );

			$qresult = mysql_query( $querystring )

			if( $qresult == "TRUE" )
			{
				print( "GOOOD<br>" );
			}
			else
			{
//					print( "BAAAD<br>");
//					exit( mysql_error( $link ) );
				}

			print( "Records added: " . mysql_affected_rows() . "<BR>" );

		}
	}
}



mysql_close($link);
?>

I've tried a number of different $querystring formats, but none of them seem to work. if I run the line $qresult = mysql_query( $querystring ), the returned page is blank, and the affected rows is -1. It won't even keep looping through print( "BAAAD\n\n" . mysql_error() ); for eact iteration. The file has about 80 lines in it. The $data var is populated.

What am I missing here?

[bubblenut]

Well, the error is that you haven't quoted the values you're trying to put into the database.
This line

$querystring = "INSERT INTO grossRevenue VALUES ( ".$items[0].", ".$items[1].", ".$items[2].", ".$line_num." )";

should be

$querystring = "INSERT INTO grossRevenue VALUES ( '".$items[0]."', '".$items[1]."', '".$items[2]."', '".$line_num."' )";

HTH
Bubble

[GarroteYou]

Thanks for the reply bubblenut.

That still didn't work, but I see where you're going. I looked over like 5 or 6 different examples of the mysql INSERT command, and never saw data in quotes.

Here is the layout of the table which I'm trying to input data to:

mysql> use movies;
Database changed
mysql> describe grossRevenue;
+---------+------------+------+-----+---------+-------+
| Field   | Type       | Null | Key | Default | Extra |
+---------+------------+------+-----+---------+-------+
| revenue | bigint(20) | YES  | MUL | NULL    |       |
| title   | char(100)  | YES  | MUL | NULL    |       |
| year    | int(11)    | YES  |     | NULL    |       |
| viewed  | tinyint(4) | YES  |     | 0       |       |
+---------+------------+------+-----+---------+-------+
4 rows in set (0.09 sec)

mysql>

Do all of the data types need quotes?

These commands are working on my server:

 mysql> SELECT * FROM grossRevenue;
Empty set (0.00 sec)

mysql> INSERT INTO grossRevenue VALUES ( 656, 'the space works', 4564, 4 );
Query OK, 1 row affected (6.49 sec)

mysql> INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( 825, 'a new entry is cool', 23423 );
Query OK, 1 row affected (1.21 sec)

mysql>

Here are some of the query strings that I have tried:

$querystring = "INSERT INTO grossRevenue VALUES ( ".$items[0].", '".$items[1]."', ".$items[2].", ".$line_num." )";

INSERT INTO grossRevenue VALUES ( 601, 'Titanic ', 1997 , 0 )

$querystring = "INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( ".$items[0].", '".$items[1]."', ".$items[2]." )";

INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( 601, 'Titanic ', 1997 )

$querystring = "INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( '".$items[0]."', '".$items[1]."', '".$items[2]."' )";

INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( '404', 'Spider-Man ', '2002 ' )

$querystring = "INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( \"".$items[0]."\", \"".$items[1]."\", \"".$items[2]."\" )";

INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( "340", "The Lord of the Rings: The Two Towers ", "2002 " )

$querystring = "INSERT INTO grossRevenue ( 'revenue', 'title', 'year' ) VALUES ( '".$items[0]."', '".$items[1]."', '".$items[2]."' )";

INSERT INTO grossRevenue ( 'revenue', 'title', 'year' ) VALUES ( '340', 'The Lord of the Rings: The Two Towers ', '2002 ' )

$querystring = "INSERT INTO grossRevenue ( \"revenue\", \"title\", \"year\" ) VALUES ( \"".$items[0]."\", \"".$items[1]."\", \"".$items[2]."\" )";

INSERT INTO grossRevenue ( "revenue", "title", "year" ) VALUES ( "340", "The Lord of the Rings: The Two Towers ", "2002 " )

$querystring = "INSERT INTO grossRevenue ( \"revenue\", \"title\", \"year\" ) VALUES ( ".$items[0].", \"".$items[1]."\", ".$items[2]." )";

INSERT INTO grossRevenue ( "revenue", "title", "year" ) VALUES ( 357, "Jurassic Park ", 1993 )

$querystring = "INSERT INTO grossRevenue VALUES ( '444', 'second cool', '56555', '5' )";

INSERT INTO grossRevenue VALUES ( '444', 'second cool', '56555', '5' )

With no variables.

$querystring = "INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( 444, 'second cool', 56555 )";

INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( 444, 'second cool', 56555 )

$querystring = "INSERT INTO grossRevenue VALUES ( 444, 'second cool', 56555, 5 )";

INSERT INTO grossRevenue VALUES ( 444, 'second cool', 56555, 5 )

What I really need is to read a good paper on the syntax rules for the mysql insert command.

Does anyone know of one that they could share? The pdf manual I got from the mysql.com site,
lists the syntax for the INSERT command on page 625, it actually begins on 626, and the PDF
page is 657. It doesn't cover quotation that I can find.

please help -

[GarroteYou]

I got it, it was the way I was referancing the server >

<?php

$db = "movies";				// var for database name
$filename = "Gross.data";	// file with all of the data
$ctr = 0;
$wh = "width=\"100%\" height=\"100%\"";

function connectDb($user, $pass, $host, $db)
{
		//this function connects to a mysql server
	$sock = mysql_connect($host, $user, $pass);

		//this function connects to a mysql database, once a server has been reached.
	if( isset( $sock ) )
	{
		if( !mysql_select_db( $db, $sock ) )
		{
			print( mysql_error() );
		}
	}

	return $sock;
}

if ( file_exists( $filename ) )
{
	$data = file( $filename );
}
else
{
	exit( "The file $filename does not exist\n\n" );
}
							// connect to DB
$socket = connectDb('movieuser','linux','localhost', $db);

							// loop through the .data file adding rows to the table
foreach ($data as $line_num => $line)
{
	$ctr++;					// increment counter

							// array of data
	$items = explode( "|", $line );

							// create query command using above array
	$qstr = "INSERT INTO grossRevenue ( revenue, title, year ) VALUES ( ".$items[0].", '".$items[1]."', ".$items[2]." )";

							//perform the query
	$query = mysql_query( $qstr, $socket );
}

print ( "<html>\n<head>\n<title></title>\n</head>\n\n" );
print( "<body bgcolor=\"#ffff00\" text=\"#330000\">\n\n" );
print( "<br><br><br>\n\n<center>\n\n" );

print ( "<table cellpadding=\"4\" cellspacing=\"0\" border=\"0\" width=\"480\">\n");

print ( "\t<tr>\n");
print ( "\t\t<td $wh align=\"center\">\n");
print ( "\t\t\t<font size=\"6\"><b>Loading Gross Revenue</b></font><br><br>\n");
print ( "\t\t</td>\n");
print ( "\t</tr>\n");

print ( "\t<tr>\n");
print ( "\t\t<td align=\"left\"><b>$ctr</b> rows inserted successfully</td>\n");
print ( "\t</tr>\n" );

print ( "\t<tr>\n");
print ( "\t\t<td $wh align=\"center\">\n");
print ( "\t\t\t<input TYPE=\"button\" VALUE=\" OK \" onClick=\"showtable.php\">\n" );
print ( "\t\t</td>\n");
print ( "\t</tr>\n" );

print ( "</table>\n\n</center>\n</BODY>\n</HTML>" );

mysql_close( $link );
?>

Thanks