MySQL Commands

[dclamp]

How do i excute simple mysql commands in php? i took a look at php.net and that just got me more confused

Like the following things:
-Add info to table
-Remove Info from table
-edit info from table
-display info from table

thanks in advanced!

[kows]

do you know the MySQL commands already..? and just want a run down of the PHP functions, or..?

here's a basic run down of the MySQL commands INSERT, DELETE, UPDATE, and SELECT. I might miss a few things, but it's just to give you an understanding of the commands if you don't know them already..

INSERT:
Used to insert new information into a table. The following example will insert the values "input1" through "input3" into the table tablename one time. The (field1, ..., field3) brackets are optional, but useful if you want to insert into only certain fields in that table, or if you want to insert information in a different order than the table's fields are in.
Syntax: INSERT INTO `tablename` (field1, field2, field3) VALUES ('input1', 'input2', 'input3');

DELETE:
Used to delete records from a table. Useful only if you want to delete records a few, or one, at a time. If you want to delete an entire table's contents, use TRUNCATE (more on it below). The following example will delete ALL records in tablename where field1 is equal to input1, which means if every record in your table has input1 as the value of field1, they will all be deleted. If you only wanted to delete one record, this can be achieved if you use LIMIT in conjunction with the query, more on that below as well!
Syntax: DELETE FROM `tablename` WHERE field1='input1'
--> TRUNCATE: Used to remove the contents of an entire table
--> Syntax: TRUNCATE `tablename`;

UPDATE:
Used to update records in a table. Syntax is very similar to DELETE, and works basically the same way. The following will change every record that has field2 being input2 to be input2a, which means if every record had field2 as input2, then all of the records in the table would be changed. This can also be controlled with the addition of LIMIT, more on that still below.
Syntax: UPDATE `tablename` SET field2='input2a' WHERE field2='input2';

SELECT:
Used to display records of a table. It can select all fields, and all records, or only a select few using the WHERE clause, and will then only display them if the conditions are met. The following will select and return all records and fields from tablename.
Syntax: SELECT * FROM `tablename`;
The following will only select field1 from tablename, and only if field3 is equal to input3.
Syntax: SELECT field1 FROM `tablename` WHERE field3='input3';

LIMIT:
This will limit the amount of records returned, and can be quite confusing at times. You can simply add "LIMIT 1" to an UPDATE, SELECT, or DELETE query and it will update, select, or delete only one record. However, if you're making something that will be displaying multiple pages, with a maximum number of records for each page, you can use "LIMIT start, numrecords". The following will select all fields and all records from tablename, and will start at the first record (0), and display 15 records at a time.
Syntax with SELECT: SELECT * FROM `tablename` LIMIT 0, 15

Remember, this was just a basic run down of commands you wanted and their syntax, if you didn't already know. Queries can get a lot more complex and be manipulated in tons of ways, using different operators and other things like that.

ok... now that that's out of the way!

Basically, using PHP with MySQL is very easy. The key functions you'll be using are mysql_query() and mysql_fetch_array(), after you've connected to your server and selected a database.

So, first off, to connect to a server and select a database you can do this:

<?php
  //connecting to
  mysql_connect("host", "user", "pass") or die("could not connect to MySQL server");
  //selecting database
  mysql_select_db("databasename") or die("could not select MySQL database");
?>

Now, to execute a query in a database, you will use mysql_query(), with the same syntax detailed above. For example, if you wanted to insert data into a database:

<?php
  //adding to
  mysql_query("INSERT INTO `tablename` VALUES('input1', 'input2', 'input3')");
?>

And that's that. The values input1 through input3 will now be inserted into tablename.

To delete from a table, or edit, it's the same.

<?php
  //deleting from
  mysql_query("DELETE FROM `tablename` WHERE field1='input1'");
  //truncating table
  mysql_query("TRUNCATE `tablename`");
  //editing records
  mysql_query("UPDATE `tablename` SET field2='input2a' LIMIT 1");
?>

Now, selecting is a little bit different, unless you're only looking for one record. You will want to create a loop when selecting multiple records so that you can display all of them at a time, otherwise it will only display the first record found (which is useful as well, especially when looking for username/password combinations when logging someone into a system). Here's an example using a while() loop that will display all records in a table:

<?php
  //build our query
  $query = mysql_query("SELECT * `tablename`");
  //loop through each record
  while($array = mysql_fetch_array($query)){
    //print out the field values and values using print_r
    echo '<pre>';
    print_r($array);
    echo '</pre>';
    //using print_r is just to show every value, if you want to select
    // --> a specific value, you can use this syntax:
    // --> $array['field1']
  }
?>

You can also use LIMITs in the while() loop query to display only a certain number of records, etc.

Hope that helps a little.

[dclamp]

that is exactly what i was looking for. It has helped me VERY much. Few questions:

1. Which one do i use to display something (ex: news) onto a page.
2. How do i execute thoes on a submit/button?

[kows]

uhh, if you read through the examples I gave you, your first question should be a no brainer. look back at it and read: SELECT

if you want to have something submit, then just build a form, an example is below:

<?
  if(isset($_POST['field1'], $_POST['field2'], $_POST['field3'])){
    //form was submitted, do some error checking on the variables submitted and add in your query here
    //for example:
    mysql_query("INSERT INTO table VALUES('" . $_POST['field1'] . "', '" . $_POST['field2'] . "', '" . $_POST['field3'] . "')");
  }else{
?>
<!-- build your HTML form here, remember to change the code above referring to field1, field2, and field3 to whichever field names you decide to use in your form -->
<? } ?>

[dclamp]

Ok thanks. sorry, i am new so i was confused.

is there a way that i can have like globally set the login details?

[kows]

yes. you can make a script that has just the connection information and then use include() to include that page into whatever other pages you want

ie:
in mysql.php you might have:

<?
  mysql_connect('host', 'user', 'pass');
  mysql_select_db('databasename');
?>

and in index.php, you might have:

<? include("mysql.php"); ?>
<html>
  <head>
    <title>blah blah</title>
  </head>
  ...

[dclamp]

thanks, you are a great help!

[dclamp]

ok, i am getting this error:

Parse error: syntax error, unexpected T_STRING in /usr/home/homtek/public_html/admin/mysql.php on line 2

in this bit of code:

<? 
  mysql_connect('localhost', 'homtek_dclamp', 'my_password'); 
  mysql_select_db('homtek_homtek'); 
?>

[CornedBee]

I see nothing wrong with that code, except that using short start tags ( <? instead of <?php ) is bad practice. Can you provide some more context, or is that all?

[dclamp]

well, i got that code from this post. that is all really.

[CornedBee]

And you copied it from your source file using copy&paste?

[dclamp]

yep. i am also having an error with the SELECT query. here is the code i am using:

<?php 
  //build our query 
  $query = mysql_query("SELECT * `tablename`"); 
  //loop through each record 
  while($array = mysql_fetch_array($query)){ 
    //print out the field values and values using print_r 
    echo '<pre>'; 
    print_r($array); 
    echo '</pre>'; 
    //using print_r is just to show every value, if you want to select 
    // --> a specific value, you can use this syntax: 
    // --> $array['field1'] 
  } 
?>

here is the error that i have:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /usr/home/homtek/public_html/admin/view_news.php on line 69

and line '69' is:

while($array = mysql_fetch_array($query)){ 

[kows]

It's referring to your query. Does the table "tablename" exist? If not, it will give you errors if you're trying to select from it.. you have to create the table before you can select from it.

As for the password thing, unless your localhost uses a different port to connect through than the default, or requires you to use an actual address (ie: mysql.someserver.com), I'm not sure what's wrong, but it's something to do with MySQL and most likely not your PHP, because the code is fine. Only thing I could POSSIBLY think of is if you have variables in your mysql_connect and are enclosing them with single quotes. If a variable is in a single quote, the variable's value will not show up, but rather the actual text you typed will show up. If you're confused on what I mean, then here's an example:

<?php
  $var = "hello!";
  echo '$var';
  //this will produce: $var
  echo $var;
  //this will produce: hello!
  echo "$var";
  //this will produce: hello!
?>

But, if you're just using plain text and entering your username/password/host directly, this should not be a problem.

[dclamp]

thanks, i got help from another forum.

[kows]

so..? what was wrong?

[dclamp]

i am not sure. some one fixed the code for me. i can post the fixed code if you want.