Can Someone Explain

Can someone explain to me when and when not to use a “,” Comma?

ie. Here it will NOT work if I try and add a , behind ComicDate)
INSERT INTO Comics
(ComicNumber, ComicName, ComicTitle, ComicAuthor, ComicDate)
VAULES
(“866”, “Detective Comics”, “The Medallion”, “Dennis O\'Neil”, “2010-9-1”);

Yet here, it requires a Coma after every line
INSERT INTO Comics SET
ComicNumber = “866”,
ComicName = “Detective Comics”,
ComicTitle = “The Medallion”,
ComicAuthor = “Dennis O\'Neil”,
ComicDate = “2010-9-1”;

It is not obvious? A comma in this context is used as a separator. You do not need a separator at the end.

So you do not need a separator where () is concerned?

If you had a bunch of desk that needed to be separated from one another would you waste separators by putting one next to the wall?

[WALL] DESK [SEP] DESK [SEP] DESK [WALL]
[WALL] [SEP] DESK [SEP] DESK [SEP] DESK [SEP] [WALL]

I am just wondering… to me it would seem logical to separate lines in a command by a comma…

I mean in the one you are calling your variables 1st then defining those variables… the 2nd you are both calling and defining your variables at once. So I would have to say at first glance it does not make sense why you separate one by a comma and not the other… but I can understand that if your variables are contained w/in a container themselves that would make sense…

let me show you how i would re-write your two examples

INSERT 
  INTO Comics 
     ( ComicNumber
     , ComicName
     , ComicTitle
     , ComicAuthor
     , ComicDate )
VALUES 
     ( 866
     , 'Detective Comics'
     , 'The Medallion'
     , 'Dennis O''Neil'
     , '2010-09-01' )
INSERT 
  INTO Comics 
   SET ComicNumber = 866
     , ComicName = 'Detective Comics'
     , ComicTitle = 'The Medallion'
     , ComicAuthor = 'Dennis O''Neil'
     , ComicDate = '2010-09-01'

my advice is not to use the second one, because it’s proprietary mysql syntax, whereas the first is standard sql

if you were ever to want to migrate your app to a different database, you want the process to go as smoothly as possible

so always use the standard sql version when mysql supports it

:slight_smile:

now here’s the first one with the extra comma you wanted to insert –

INSERT 
  INTO Comics 
     ( ComicNumber
     , ComicName
     , ComicTitle
     , ComicAuthor
     , ComicDate
     ,    )
VALUES 
     ( 866
     , 'Detective Comics'
     , 'The Medallion'
     , 'Dennis O''Neil'
     , '2010-09-01'
     ,   )

can you see now why that would give a syntax error?

the comma has nothing to do with the lines, and everything to do with acting as a separator between identifiers or values

Actually I was not talking about putting the comma inside of the container B VALUES[/B] I was talking about putting it on the outside of the container B, VALUES[/B], but like I said, I can understand the purpose of not needing to separate containers when they are already separated by declarations(?).

that would be a syntax error

you don’t see anything like it in other sql statements either –

SELECT foo
     , bar
     , 
  FROM daTable
     , 
 WHERE qux = 'fap'

similar syntax error

Not wishing to dilute the topic, but having a trailing comma separating values in SQL will not work, it is permitted in php arrays.


insert into mytable values('a', 'b', 'c', 'd'); // correct
insert into mytable values('a', 'b', 'c', 'd',); // wrong

Whereas:


$thing = array( 'a', 'b', 'c', 'd'); // correct
$thing = array( 'a', 'b', 'c', 'd', ); // also permitted

While this might seem off-topic it is nevertheless something to bear in mind and watch out for when you start to prepare arrays of values in PHP to send to SQL to do your bidding.

I was wondering if it is possible to insert info into multiple DB’s at one time. ie.
I have a DB of comics, with titles and numbers
and then I have another DB (or two) with Authors, Artist’s and so forth… Is it possible, as you add the comic info in, can you add info into the Author and Artist DB’s at the same time from one insert tag?

it’s called a statement, and no, you can’t

each INSERT statement is for one table only

Ok, just wondering if you where able to tie 2-3 tables with one insert… imagine the time one could save…!

This is my first attempt at relational database, I am getting this error(s):

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in /Library/WebServer/Documents/comics/index.php on line 92
Add your own comic

Here are all the comics in the database:

Notice: Undefined variable: comicinfo in /Library/WebServer/Documents/comics/comics.html.php on line 12

Warning: Invalid argument supplied for foreach() in /Library/WebServer/Documents/comics/comics.html.php on line 12

<body>
		<p><a href="?addcomic">Add your own comic</a></p>
		<p>Here are all the comics in the database:</p>
		<?php foreach ($comicinfo as $comicinfo): ?>
			    <form action="?deletecomic" method="post"> 
				<blockquote>
					<p>
						<?php echo htmlspecialchars($Comics['ComicNumber'], ENT_QUOTES,
								'UTF-8'); ?>
						<?php echo htmlspecialchars($Comics['ComicName'], ENT_QUOTES,
								'UTF-8'); ?>
						<?php echo htmlspecialchars($Comics['ComicTitle'], ENT_QUOTES,
								'UTF-8'); ?>
						<?php echo htmlspecialchars($Comics['ComicDate'], ENT_QUOTES,
								'UTF-8'); ?>
						<input type="hidden" name="id" value="<?php
								echo $Comics['id']; ?>"/>
				<!--		<input type="submit" value="Delete"/> -->
						(by <?php echo htmlspecialchars($Comics['ComicAuthor'], ENT_QUOTES,
								'UTF-8'); ?>)
					</p>
				</blockquote>
			</form>
		<?php endforeach; ?>
	</body>
</html>

	<body>
		<form action="?" method="post">
			<div>
				<label for="ComicNumber">Comic Number:</label>
				<textarea id="ComicNumber" name="ComicNumber" rows="1" cols="10"></textarea>
				<label for="ComicName">Comic Name:</label>
				<textarea id="ComicName" name="ComicName" rows="1" cols="10"></textarea>
				<label for="ComicTitle">Comic Title:</label>
				<textarea id="ComicTitle" name="ComicTitle" rows="1" cols="10"></textarea>
				<label for="ComicDate">Comic Date:</label>
				<textarea id="ComicDate" name="ComicDate" rows="1" cols="10"></textarea>
			</div>
			<div><input type="submit" value="Add"/></div>
		</form>
	</body>
</html>
if (isset($_POST['ComicNumber']))
{
	$comicinfo = mysqli_real_escape_string($link, $_POST['ComicNumber']);
	$sql = 'INSERT INTO Comics SET
			ComicNumber="' . $ComicNumber . '",
			ComicName="' . $ComicName . '",
			ComicTitle="' . $ComicTitle . '",
			ComicDate="' . $ComicDate . '"';
	if (!mysqli_query($link, $sql))
	{
		$error = 'Error adding submitted Comic: ' . mysqli_error($link);
		include 'error.html.php';
		exit();
	}

	header('Location: .');
	exit();
}

if (isset($_GET['deletejoke']))
{
	$id = mysqli_real_escape_string($link, $_POST['id']);
	$sql = "DELETE FROM joke WHERE id='$id'";
	if (!mysqli_query($link, $sql))
	{
		$error = 'Error deleting joke: ' . mysqli_error($link);
		include 'error.html.php';
		exit();
	}

	header('Location: .');
	exit();
}

$result = mysqli_query($link,
		'SELECT Comics.id, ComicNumber, ComicName, ComicTitle, ComicDate, ComicAuthor
		FROM Comics INNER JOIN ComciAuthor
			ON AuthorId = ComicAuthor.id');
/*if (!$result)
{
	$error = 'Error fetching Comics: ' . mysqli_error($link);
	include 'error.html.php';
	exit();
}
*/
while ($row = mysqli_fetch_array($result))
{
	$Comics[] = array('id' => $row['id'], 'ComicNumber' => $row['ComicNumber'],
			'ComicName' => $row['ComicName'], 'ComicTitle' => $row['ComicTitle'],
			'ComicDate' => $row['ComicDate'], 'ComicAuthor' => $row['ComicAuthor']);
}

include 'comics.html.php';
?>

The bit you’ve commented out, uncomment it:


/*if (!$result)
{
    $error = 'Error fetching Comics: ' . mysqli_error($link);
    include 'error.html.php';
    exit();
}

*/

It’ll tell you what’s going wrong in the query.

Hint: look at the spelling of ‘ComciAuthor’ in your SQL statement :slight_smile:

Count the number of commas you used in both queries: FOUR. In the second query you used a semi-colon, not a comma after ComicDate. The semi colon is optional, it is used to indicate that the query ends here and it may or may not be followed by another query.