Using PHP to make mySQL database/tables

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Using PHP to make mySQL database/tables

Post by austin0369 »

Hey,

Ive been looking threw posts, and all the posts bascially have how to create a table under a database. But i want to make a database with the username as "iwillchange" and a password "soon". The database would be Client, and the table would be Users, then the stuff under that would be Username (primary) password, and email. I know I have to start by connecting to my server, but what do i do after that? Thanks

-Austin
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

some info

Post by phpScott »

The http://www.mysql.com site is a great place for all sorts of info.
Try http://www.mysql.com/doc/en/Database_use.html for info on how to create a db, set permissions, and creating tables.

phpScott
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Post by austin0369 »

Ok i came up witht his..but it doesnt work

Code: Select all

<?php
$link = mysql_connect(localhost, USERNAME, PASSWORD) or die(mysql_error());  

$sql = "CREATE DATABASE thistest
CREATE TABLE thistestis
username VARCHAR (40) NOT NULL,
password VARCHAR (40) NOT NULL,
PRIMARY KEY (username))"; 

// Run the query 
mysql_query($sql) or die(mysql_error().'<p>'.$sql.'</p>');
?>
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Privileges

Post by phpScott »

If you are running this on a remote server the username and password might not have the right Privileges to create databases and tables and the like.

Code: Select all

<?php
$sql="SHOW GRANTS FOR link@localhost";
$result = mysql_query($sql);
if (!$result)
{
  $msg = 'Error 411 - ' . mysql_error();
  echo $msg;
  return false;
}
  while($row = mysql_fetch_row($result))
  {
    echo $rowї0]."<br>";
  }
?>
insert how you make your db connection a the top of this script then run it. It should return what permmsions you have kinda like,
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'link'@'localhost' IDENTIFIED BY PASSWORD '3dd401ac51a8df42'
It will let you know if you can create db's table's and the like.

phpScott
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Post by austin0369 »

Sorry, i have no idea what u8 mean by what u posted..either way it doesnt seem to work for me..this is what i have:

Code: Select all

<?php
$connection = mysql_connect(localhost, FTPusername, FTPpassword) or die(mysql_error());  
$sql="SELECT * FROM localhost"; 
$result = mysql_query($sql, $connection); 
if (!$result) 
{ 
  $msg = 'Error 411 - ' . mysql_error(); 
  echo $msg; 
  return false; 
} 
  while($row = mysql_fetch_row($result)) 
  { 
    echo $rowї0]."<br>"; 
  } 
?>
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

Post by phpScott »

try this.
remeber to put in your username and password for the db that you are currently using.

Code: Select all

<?php 
$connection = mysql_connect(localhost, FTPusername, FTPpassword) or die(mysql_error());
$sql="SHOW GRANTS FOR link@localhost"; 
$result = mysql_query($sql); 
if (!$result) 
&#123; 
  $msg = 'Error 411 - ' . mysql_error(); 
  echo $msg; 
  return false; 
&#125; 
  while($row = mysql_fetch_row($result)) 
  &#123; 
    echo $row&#1111;0]."<br>"; 
  &#125; 
?>
change link to your username. This will show you what you are allowed to do, such as select, update, delete, grant, and so on.
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Post by austin0369 »

Yes, but I want to make a database using php...
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Post by austin0369 »

anyone..?
User avatar
mydimension
Moderator
Posts: 531
Joined: Tue Apr 23, 2002 6:00 pm
Location: Lowell, MA USA
Contact:

Post by mydimension »

can we see the current code you are using?

there is also the possibility that you are not allowed to create databases on your server (see phpScott's post above)
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

a little explanation

Post by phpScott »

you can use almost all mySql commands, including create database, create table, and the like in php and executing the query like you normally do for update, inserts, and selects.
There is one little problem thought, you have to have permission, or privilage in MySql case, to do those commands.

If you run the script I posted then you will see what you are allowed to do. If it isn't listed then you can not perform that action. It is as simple as that, either you are allowed to or not.

I have on my local development machine don't exactly what you are trying to do, but I gave myself permission to do so.

However when I tried to do a create table on my host, it came up with an error to the effect that the user didn't have permission to do so.

It is a safety feature so that If you want you could create two different users accessing the same db with one allowed to enter and delete info, where another one can only do selects, to view it.

Hope this clarifies things a little for you. By the sounds of it you will have to contact your host to see what you can or can't do.

phpScott
austin0369
Forum Newbie
Posts: 19
Joined: Tue Aug 20, 2002 5:56 pm

Post by austin0369 »

K thanks scott
Post Reply