Quick sql question

[leisnerr]

Ok, so I'm advacing..kind of lol. I'm having trouble with access to my database. I created a test database to login to which worked. I even put in the wrong password to make sure it would tell me that there was an error and that was working fine. Then I tryed to create a new db from that existing connection, it says "Access denied for user XXXXXXX@localhost to database 'my_db'. This is the code I'm working with right now if someone can tell me where I'm going wrong with this. Thanks again in advance, I really appreciate you guys helping me to learn PHP.

 

<?php

 

 

$con = mysql_connect("localhost", "leisnerr_admin", "****");

if (!$con)

{

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

}

 

 

if (mysql_query("CREATE DATABASE my_db" ,$con))

{

echo "Database created";

}

else

{

echo "Error creating database: " . mysql_error();

}

 

 

 

 

 

mysql_close($con);

?>

 

[paul2463]

does the username "leisnerr_admin" have permissions sufficient enough to CREATE databases??

[leisnerr]

When I made the test db I'm working with I gave admin 'All Privelages' yes. That's why I'm not sure why it says I can't do it.

[leisnerr]

[per1os]

I think you are just giving him all privileges for that current database.

 

You need him to have all privileges via MySQL main portion.

[leisnerr]

Would that be my regular login info? Cause I tried that as well and I still got an error.

[bwochinski]

First of all, yes that user has "ALL PRIVILEGES" only for the database specified.  Also, I believe most hosts that use cPanel or their own control panel for their mySQL management do not allow a "CREATE DATABASE" query at all.  Whatever their reasons, they don't want databases created by scripts on the fly.

[leisnerr]

Umm, well with that new info can someone enlighten me as to how I could go about being able to script to create my db's? Or would you advise me to just create them through cpanel and use single person access?

[bwochinski]

what is the application you're designing that you want to create new databases? 

[leisnerr]

I'm not doing it for a specific purpose. I started learning PHP like 2 days ago and I'm just trying to advance my knowledge. I've been following through w3schools.com to learn stuff and the tutorial or w/e I'm following wants the script to create a new db named 'my_db'. I'm just doing this for general knowledge so that hopefully I can be as good as you guys some day at coding. :-P

[bwochinski]

ahhh I see.  Well, just so you know, I've never actually used a "CREATE DATABASE" statement myself because of the restrictions hosts usually put on mySQL.  If you had a dedicated server, then you would have full control over these kinds of things, but that's a fairly hefty cost and certainly not needed.

 

For the tutorial, just skip the create database part and set up the database through cPanel.  You should be pretty much set thereafter.

[leisnerr]

Ok thanks. I know most of the MySQL commands, would it be easier to add info by using a web panel to INSERT and stuff or should I write a script for it? Like let's say I wanna setup a db for a car dealership. I think it would be easier to put it in through the webpanel instead of scripting it out to input it. But there may be some advantage I'm overlooking if somebody want's to comment. And yes I plan on using php to insert 'single' information. Like when someone register's I plan on having it insert $username and such etc. I mean for basic setup's I should prolly just enter the data manually correct?

[bwochinski]

That's mostly a matter of preference.  I assume you're talking about phpMyAdmin as the web panel, which works very well for easy database management.  I often use it for seeding databases with test data and such.  If you've got a lot of data though, you can just run SQL statements through phpMyAdmin so that you don't have to keep revisiting the insert page.

[leisnerr]

Okay, well now that I tried skipping the creating db command I ran into another problem. It's not giving me error specifics. Personally I think the problem is with my If/Else statement but I'm not sure. I wanted to add a table through the script. I'm not too worried over it because I can do that easier in phpMyAdmin(yes by the way thats what I meant when I said web panel), but it would be nice if someone could tell me what exactly the problem is that I'm having here. Thanks again.

 

<?php

 

$con = mysql_connect("localhost", "leisnerr_admin", "********");

if (!$con)

{

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

}

else

{

echo "Error creating table: ". mysql_error();

}

 

mysql_select_db("mydb", $con);

$sql = "CREATE TABLE Person

(

FirstName varchar(15),

LastName varchar(15),

Age int

)";

mysql_query($sql,$con);

 

 

 

mysql_close($con)

?>

[bwochinski]

First of all... are you sure the table hasn't been created?  Because there is a problem with your if/else statement: Namely that there is an "else" condition at all.  The if checks if the connection has been made, if not, it displays "could not connect", but if the connection was made, then the "else" condition is executed, which displays "error creating table" before it even gets to the code to try and create it.  That is just echoed though, so the script will still continue to execute the table creation query.

 

You probably aren't getting any error specifics because the "mysql_error()" fuction isn't returning anything. (because there wasn't an error!)

[leisnerr]

Lol! Hmm..does it count as an error to troubleshoot myself?! Anyways, I checked in phpMyAdmin and this is what it's showing.

 

[Barand]

Moved the error message for table creation.

 

Tables need a unique identifier for each row , so I added "id" column

 

int is 4 bytes and holds up to 2 billion, so unless it's a db of dinosaurs

tinyint will suffice for age (1 byte max val = 127).

<?php

$con = mysql_connect("localhost", "leisnerr_admin", "***");
if (!$con)
   {
   die('Could not connect: ' . mysql_error());
   }

mysql_select_db("mydb", $con);
$sql = "CREATE TABLE Person
(
id int not null auto_increment PRIMARY KEY,                 
FirstName varchar(15),
LastName varchar(15),
Age tinyint                                                 
)";
mysql_query($sql,$con) or die ("Error creating table: ". mysql_error());



mysql_close($con);       // not really necessary as connection close at end of script
?>

[bwochinski]

I believe the problem is with your "mysql_select_db()" function.  You tell it to select "mydb", while phpMyAdmin shows the DB name as "leisnerr_mydb".  Most hosts are going to prepend your username to the databases to prevent name collisions on shared servers.

[leisnerr]

Yes, actually you were both right. I can't believe I forgot to identify the Primary key..I haven't use SQL in awhile. And yes it did tag my db with a prefix when I didn't want it to. Thanks for pointing that out both of you.

[leisnerr]

I tryed making a form that would send data from the submit htm page over to my php page that connects to the db to insert some data into the table that was made now. I'm getting an error but I'm not sure what is going wrong. I double checked it and the code looks alright to me I think I must be forgetting something.. Here is the code:

The data from the htm file:

<html>
<body>
<table border="1" cellspacing="1" cellpadding="1">
<tr>
<td><form action="insertdata.php" method="post">
Firstname:</td>
<td><input type="text" name="firstname"></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input type="text" name="lastname"></td>
</tr>
<tr>
<td colspan="2"><input type="submit"></td>
</tr>
</table>
</body>
</html>

This is the php file to recieve the info

<?php

$con = mysql_connect("localhost", "leisnerr_admin", "****");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}


mysql_select_db("leisnerr_mydb", $con);

mysql_query("INSERT INTO Person (FirstName, LastName, Age)
VALUES ('$_POST[firstname]', '$_POST[lastname]', '$_POST[age]')");

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Information Added";


mysql_close($con);
?>

[leisnerr]

Now that I think about it do I need to define the info coming into the php page as variable's before I plug them into a query?

[Barand]

It's not a good idea to put anything that originates from the client direct into a query (ie, GET, POST, COOKIE data)

 

Use a data-cleansing function to guard against sql injection, eg

 

<?php
function clean ($data) {
    $res = get_magic_quotes_gpc() ? $data : mysql_real_escape_string($data);
    $res = strip_tags($res);
    return $res;
}
?>

 

So

<?php
$fn = clean($_POST['firstname'];   // always used quotes with string indexes
$ln = clean($_POST['lastname'];
$age = clean($_POST['age'];

$sql = "INSERT INTO Person (FirstName, LastName, Age)
          VALUES ('$fn', '$ln', '$age')";

if (!mysql_query($sql,$con))
{
die('Error: ' . mysql_error());
}
echo "Information Added";
?>

 

[per1os]

Just a quick note, if you are handling templates etc that contain html, the strip_tags is probably not what you want, it will remove all HTML tags from a string.

 

Just an FYI =)

[Barand]

Get them to use bbcode then you have control

[leisnerr]

Whoa, umm you guys are getting a little advanced for me lol. If you can explain in a little more 'common knowledge' term for us non-expert's what you mean. Ive never heard of get_magic_quotes_gpc or the mysql_real_escape_string and strip_tags. If you could explain a little bit more in depth that would be awesome.