Php / Mysql Help

[danthocker]

Ok I'm trying to input information into my database from an html form. The following is my php code.

$username="*";
$password="";
$database="***";

$owner=$POST['Owner'];
$char=$POST['Charecter'];
$join=$POST['Joined'];
$title=$POST['Title'];
$corp=$POST['Corp'];
$cap=$POST['Capital'];
$spo=$POST['Sponsor'];
$fleet=$POST['FleetParticipation'];
$kill=$POST['Kills'];
$user=$POST['UserID'];
$api=$_POST['APIKey'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die("Unable to select database");

$query = "INSERT INTO Members VALUES ('','$owner','$char','$join','$title','-TT-','Y','$spo','$fleet','$kill','$user','$api')";
msql_query($query);

mysql_close();

I then get the following error when I attempt to submit the info.

Fatal error: Call to undefined function msql_query() in /home/phptest/insert.php on line 22

I'm not sure what the problem is. I've been using a web tutorial to teach myself this, and as far as I can tell I've done everything the way it did.

[djjjozsi]

try mysql_query() instead!

troubleshooting:

mysql_query($query) or die(mysql_error()." in query:". htmlspecialchars($query));

use at least a basic mysql injection prevention.

Read here more:

htmlspecialchars()
mysql_error()
mysql_query()
mysql_real_escape_string()

[danthocker]

Thanks man, that fixed that problem but yet another one arises. I am now getting the following error.

Unknown column 'N' in 'field list' in query:INSERT INTO Members VALUES ('','test','tester','01/01/2001','testing',N,T,'0','0','','0')

I see what the problem is, but I'm not sure how to fix it. My radio buttons for the spots where the value is N and T are not putting the values in single quotes like everthing else. Any suggestions.

Here's the code from the html

<input type="radio" name="Capital" value="Y" id="Capital_0" />
      Yes</label>
    <input type="radio" name="Capital" value="N" id="Capital_1" />
    <label>
    No</label>

And here's the code from the php

$query = "INSERT INTO Members VALUES ('','$owner','$char','$join','$title',$cap,$corp,'$fleet','$kill','$user','$api')";
mysql_query($query) or die(mysql_error()." in query:". htmlspecialchars($query)); 

[johanafm]

And that's because you haven't quoted them. See the difference between '$title' and $cap?

"... '$title',$cap,$corp,'$fleet', ...";

Also, the date will not work, unless you save it to a varchar or text column. SQL dates have the form YYYY-MM-DD

[danthocker]

So I fixed that one, just a dumb mistake on my part. Now I have another problem. I am again not sure how to fix. I am attempting to output the data from my database into a table. It sets up the table properly, but does not actually display the data, and yes there is data in the database.

 <?
 $username="brutestr_admin";
$password="203232";
$database="brutestr_Membership";
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM Members";
$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

$i=0;

while ($i < $num) {

$own=mysql_result($result,$i,"Owner");
$char=mysql_result($result,$i,"Charecter");
$join=mysql_result($result,$i,"Joined");
$title=mysql_result($result,$i,"Title");
$cap=mysql_result($result,$i,"Capital");
$corp=mysql_result($result,$i,"Corp");
$spo=mysql_result($result,$i,"Sponsor");
$fleet=mysql_result($result,$i,"Fleet Participation");
$kill=mysql_result($result,$i,"Kills");
$user=mysql_result($result,$i,"User ID");
$api=mysql_result($result,$i,"API Key");
?>
<tr>
<td><? echo $own; ?></td>
<td><? echo $char; ?></td>
<td><? echo $join; ?></td>
<td><? echo $title; ?></td>
<td><? echo $cap; ?></td>
<td><? echo $corp; ?></td>
<td><? echo $spo; ?></td>
<td><? echo $fleet; ?></td>
<td><? echo $kill; ?></td>
<td><? echo $user; ?></td>
<td><? echo $api; ?></td>
</tr>

<? 
$i++;
}

echo "</table";
?>

[johanafm]

PHP parsing should work assuming you have enabled php short tags. I'd still recommend using <?php though.

I'd change the following

// mysql_connect(localhost,$username,$password);
// Keep the link resource around.
$link = mysql_connect(localhost,$username,$password);

// @mysql_select_db($database) or die( "Unable to select database");
// No reason to use the error supression operator. On a general basis, it's evil.
if (!mysql_select_db($database)) {
	echo 'Unable to select database<br/>';

// Knowing what went wrong is nice
error_log(mysql_erno() . ': ' . mysql_error());
exit;		// or perhaps rather continue displaying whatever static content your site has.
}

$result = mysql_query($query);
// If the query fails, $result is not a result set resource, it's boolean false
if (!$result) {
	echo 'An error occured<br/>';
	// Decide what else to do.
}
else {
	// mysql_close();
	// I've had problems with mysql_close() not working properly without specifying the DB link
	/*	Also, if you need to perform any more queries, closing and opening DB
		connections is not efficient. Rather stick to one DB connection per request */
	mysql_close($link);

// $num =, $i = etc
/* If you control your loop by incrementing one variable and comparing to a max,
   I recommend using a foor loop. It's more common, but especially you don't risk
  forgetting about incrementing $i somewhere. */
for ($i = 0, $num = mysql_num_rows($result); $i < $num, ++$i) {
	// rest of the stuff inside your loop.		
}

// However, to reduce the amount of code you need to write replace the above with, either this, if the DB stores the columns in the same order you want them
while ($row = mysql_fetch_assoc($result)) {
	echo '<tr>';
	foreach($row as $col) {
		echo '<td>'.$col.'</td>';
	}
	echo '</tr>';
}

// else, use this instead
$col_order = array('Owner', 'Character', 'Joined', 'Title' /* etc */);
while ($row = mysql_fetch_assoc($result)) {
	echo '<tr>';
	foreach($col_order as $col) {
		echo '<td>'.$row[$col].'</td>';
	}
	echo '</tr>';
}

// echo "</table";
echo "</table>";

}

[danthocker]

Well i did everything up through the for loop replacing, using my code from inside the loop, as I'm not sure I completely understood the code you wanted me to do. It still does not display any data in the rows when I run the page. Here's the new code I added .

	<?PHP
$link = mysql_connect(localhost,$username,$password);
	if (!mysql_select_db($database)) { 
    	echo 'Unable to select database<br/>'; 
    	error_log(mysql_erno() . ': ' . mysql_error());
    	exit;
    }
	$result=mysql_query($query);

if (!$result) {
	echo 'An error occured<br/>';
}
else {
	mysql_close($link);
}

for ($i = 0, $num = mysql_num_rows($result); $i < $, ++$i) {
while ($i < $num) {

$own=mysql_result($result,$i,"Owner");
$char=mysql_result($result,$i,"Charecter");
$join=mysql_result($result,$i,"Joined");
$title=mysql_result($result,$i,"Title");
$cap=mysql_result($result,$i,"Capital");
$corp=mysql_result($result,$i,"Corp");
$spo=mysql_result($result,$i,"Sponsor");
$fleet=mysql_result($result,$i,"Fleet Participation");
$kill=mysql_result($result,$i,"Kills");
$user=mysql_result($result,$i,"User ID");
$api=mysql_result($result,$i,"API Key");
?>
<tr>
<td><font face="Arial Black, Gadget, sans-serif"><? echo $own; ?></font></td>
<td><? echo $char; ?></td>
<td><? echo $join; ?></td>
<td><? echo $title; ?></td>
<td><? echo $cap; ?></td>
<td><? echo $corp; ?></td>
<td><? echo $spo; ?></td>
<td><? echo $fleet; ?></td>
<td><? echo $kill; ?></td>
<td><? echo $user; ?></td>
<td><? echo $api; ?></td>
</tr>
<?PHP
}
    }
?> 

[rincewind456]

I think you need to do something more like this

<?php
$link = mysql_connect(localhost,$username,$password);
if (!mysql_select_db($database)) {
	echo 'Unable to select database<br/>';
	error_log(mysql_erno() . ': ' . mysql_error());
	exit;
}
$result=mysql_query($query);

if (!$result) {
	echo 'An error occured<br/>';
}
$tableRow = "";
while($row = mysql_fetch_array($result)){
	$tableRow.="<tr>
   	<td>".$row['Owner']."</td>
	<td>".$row['Charecter']."</td>
	<td>".$row['Joined']."</td>
	<td>".$row['Title']."</td>
	<td>".$row['Capital']."</td>
	<td>".$row['Corp']."</td>
	<td>".$row['Sponsor']."</td>
	<td>".$row['Fleet Participation']."</td>
	<td>".$row['Kills']."</td>
	<td>".$row['User ID']."</td>
	<td>".$row['API Key']."</td>
	</tr>";
}

echo $tableRow;

mysql_close($link);
?>

In your code your closing the mysql result before you use it.

You may have problems with the field names in your sql table, I don't think it's a very good idea to have spaces in the field names.

Also you should remove the font information from the td and use css to style it.

[danthocker]

That doesn't seem to work either.

[djjjozsi]

do you get an error message? be specific.
and as mentioned before remove the whiespaces from your mysql field names.

troubleshooting:

mysql_query($query) or die(mysql_error() . " - " . $query);

[johanafm]

How about trying this to see what data you actually get:

while($row = mysql_fetch_array($result)){
	print_r($row);
}

As for my foreach approach

$row = array('one' => 'A', 'three' => 'C', 'two' => 'B');
$column_order = array('one', 'two', 'three');

// Foreach goes through all elements in an array
foreach($row as $column) {
	echo $column . ' ';
}
// Output: A C B


// Go through the elements in $column_order. Use those values to retrieve elements from $row
foreach($column_order as $index) {
	echo $row[$index] . ' ';
}
// $index will be: one, two, three
// output: A B C

And I meant that you either use your approach

for ($i = 0, $num = mysql_num_rows($result); $i < $num, ++$i) {
	$own = mysql_result($result, $i, "Owner");
}

Or remove that part completely and instead go with[code=php]
while ($row = mysql_fetch_assoc($result)) (
	foreach(...)
}
Closing the db connection shouldn't be a problem. As long as you do not mysql_free_result($result), this data should be available.

[johanafm]

And turn on error reporting.

    for ($i = 0, $num = mysql_num_rows($result); $i < $, ++$i) {

You missed num in $i < $ and it should be followed by ; not ,

    for ($i = 0, $num = mysql_num_rows($result); $i < $num; ++$i) {

And remove the while loop inside the for loop. Or remove the for loop. Choose whichever you wish to use. It's a matter of preference more than right or wrong. Using bBoth is wrong in this case though.