Common PHP/MySQL Procedures

compuXP · 07-11-2006, 09:42 PM

This is how I learned to do these things; works very well and all methods are very flexible.

CONNECTING TO DATABASE
----------------------------

PHP Code:

   mysql_connect("localhost", "db_username", "db_pwd"); //Login as a database user. set this up in CPanel
mysql_select_db("db_name"); //select the database associated with the user

INSERTING INTO A DATABASE TABLE
-------------------------------------

PHP Code:

   mysql_query("INSERT INTO table_name VALUES(NULL, '$first_field', '$second_field', '$etc')");

Only use that NULL if you have an auto_increment (like an 'id' field) or something.

MODIFYING A DATABASE TABLE
--------------------------------

PHP Code:

   mysql_query("UPDATE table_name SET field_name='$variable',different_field='$anotherone' WHERE id='$thisID' LIMIT 1");

You can modify the "WHERE" clause to fit any criteria you need, and the LIMIT to whatever you want.

RETRIEVING FROM A DATABASE TABLE
---------------------------------------
The more complex part of database interaction... we're also going to set up the variables to USE the values we retrieve in a list, etc.

PHP Code:

   $results = mysql_query("SELECT * FROM table_name WHERE id='$variable' LIMIT 5"); //This gets the data we want

//Now we need to get and display the data, very simply.
//The $row variable is an array that holds each row's data through the while loop.
while ($row = mysql_fetch_array($results))
{
$showThis = $row['field_name'];
echo($showThis."<br>");
}
//Now, each time we go through a row in the database,
//(5 in this case, because of our LIMIT clause in the query),
//it shows the value of a table field named "field_name" then goes
//to another line for the next table row.

That was the most complex part of this actually simple process.

DELETING A ROW FROM A TABLE
---------------------------------

PHP Code:

   mysql_query("DELETE FROM table_name WHERE id='$thisID' LIMIT 1");

CLOSING DATABASE CONNETION
---------------------------------

PHP Code:

   mysql_close();

Questions? Examples needed? "How would I" questions? Reply...

-Matt

jumpenjuhosaphat · 07-11-2006, 10:01 PM

hey, thanks for that, you included some info I never seen in the several tutorials I've read so far. Good info, I'll refer back to it alot. Thanks again.

jumpenjuhosaphat · 07-11-2006, 10:26 PM

How would I...Take information in a form, pass it to the php script, and use that information with a table..okay, heres the deal. I want to enter a name into a form, and a file to upload. The name will be selected from a pre-determined drop list. So, when entering these into a new table, how do I use the table.row's primary key as part of the new filename? So..
I enter: grumpy.gif - image to upload
and: oldpeople - save file as(from a drop down)
submit

find oldpeople entry on the directory table
point to the table that contains all the oldpeople image info
insert the form data in the next available row(what row number?)-this is where I need help. How do you tell what the next available row is going to be in the selected table?
from there:
move the image to an image folder with the name"oldpeople_$nexttablerow.$fileextension"
and send that new file name to the oldpeople table

Also, is there a simple way to extract the file extension so I can rename the file appropriately?

compuXP · 07-12-2006, 12:10 AM

First, your form tag should look like this:

<form method="post" action="php_file_name.php">

Usually you use POST.

When you press a submit button, it loads the form action file. If you add a "name" attribute to your form elements (e.g. <input name="put_name_here">), those names turn into PHP variables. They're in an array as in: $_POST['put_name_here'], but you can access them just like any other variable: $put_name_here, and work with them that way.

So, if you have a dropdown list named "name", then the PHP variable will be $name.

If you are uploading a file, don't forget the following in the FORM tag!!

<form method="post" action="php_file_here.php" enctype="multipart/form-data">

I BELIEVE that is the correct enctype but I'm not 100% sure. Maybe somebody could verify this.

You can upload images into a database, but it is more recommended that you do it into the file system.

As for what you say here:

Quote:

find oldpeople entry on the directory table
point to the table that contains all the oldpeople image info
insert the form data in the next available row(what row number?)-this is where I need help. How do you tell what the next available row is going to be in the selected table?
from there:
move the image to an image folder with the name"oldpeople_$nexttablerow.$fileextension"
and send that new file name to the oldpeople table

I'd rather you just tell me what you want to do, rather than what procedures you take. It'll be easier for me to "get".

Quote:

So, when entering these into a new table, how do I use the table.row's primary key as part of the new filename?

Usually, the primary key is just an auto_increment "id" field... at least, in proper construction of a database. IMO.

Quote:

this is where I need help. How do you tell what the next available row is going to be in the selected table?

You don't need to, just run the INSERT query. If you want to get details of the last row in the database, do a SELECT query like:

SELECT * FROM table_name ORDER BY id DESC LIMIT 1

That is, if you are using IDs. If you aren't, well, it's a little more difficult. Nothing better comes to mind.

Quote:

Also, is there a simple way to extract the file extension so I can rename the file appropriately?

Yes. Are you trying to REPLACE the extention? Or re-format an image? Or just READ what the extention is? If you rename a .jpg to a .gif for example, it may not show properly.

jumpenjuhosaphat · 07-12-2006, 02:06 AM

I can verify that that's the right encryption type. The table I am accessing has a primary key thats set auto_increment. The name of the primary field is id.
I am not changing the extension of the image, I want to change the file name, so I want to strip the original name out, and replace it with a common name with the tables id # concantenated at the end...like this....
thisgif_01.gif - thisgif_02.gif - thisgif_03.gif - thisgif_04.jpg and so on.

Okay, so what I got so far is a form like following
name:
keywords:
metadescription:
imagename:
description:

In php, I want to put most of that into a table row that corresponds to the name: form field. In other words a table named $name. The name tables have all already been created, and the name: field is a drop down list.
So...most of it will be easy, cause I just have to put the correct info into the correct field of the table. The hard part is changing the images original name to be $name.$idnumberintable.'.'.$originalextension. For example...I upload an image named boogy.jpg and the name field is harlem_globetrotters. The next available id# is 3, sooo....I want it to be renamed "harlem_globetrotters_03.jpg"

Also, i inserted the code you gave me from above.. $var=mysql_query('SELECT BY DESC ORDER');, is there a way to echo the contents of that varialble if their isnt any records in the table yet?

rohan2kool · 07-12-2006, 08:19 AM

use var_dump() function. But as it doesn't contain any value, it will most probably be a null value.

Frankly, I didn't really understand what you intended to do, but I gave my answer on the basis of whatever I did understand.

jumpenjuhosaphat · 07-12-2006, 08:47 AM

I apologize, I still am new to this, so explaining myself can be difficult. I appreciate you taking the time to dredge through my nonsense and coming up with the best answer you could. If anyone has tips as to how to explain myself better, I am all ears.

compuXP · 07-12-2006, 03:33 PM

Quote:

Also, i inserted the code you gave me from above.. $var=mysql_query('SELECT BY DESC ORDER');, is there a way to echo the contents of that varialble if their isnt any records in the table yet?

First, that's not the query I gave you at all, that won't work.

And, no, if there's nothing in the table it can't be displayed...

jumpenjuhosaphat · 07-12-2006, 06:22 PM

SELECT * FROM table_name ORDER BY id DESC LIMIT 1

Sorry, I wasn't being literal. I used the exact query you gave me, I will be more specific next time. So I did all the stuff I was asking, would it help others if I posted how I did it?

compuXP · 07-12-2006, 07:56 PM

In a new thread, yeah, they'd find that helpful.

jumpenjuhosaphat · 07-12-2006, 08:00 PM

Okay, I will do that then. I am going to fine tune the whole script, then post it probably tonight or tommorow.

pipeten · 10-14-2006, 08:17 PM

You should extend this list, seems pretty useful so far.

I find print_r() one of the most useful functions (to display the contents of an array or object).

Tekime · 01-15-2007, 03:57 AM

Double quotes must be parsed for variables; I diligently use single quotes so that I am in the habit of writing "faster by default".

Original

PHP Code:

   mysql_query("INSERT INTO table_name VALUES(NULL, '$first_field', '$second_field', '$etc')");

Faster

PHP Code:

   mysql_query('INSERT INTO table_name VALUES(NULL, "' . $first_field . '", "' . $second_field . '", "' . $etc . '")');

I also define table names for scalability. This can save a LOT of time if an application starts to get big and muddy and you need to change a table name.

PHP Code:

   define('TBL_NAME', 'tbl_name');
mysql_query('INSERT INTO ' . TBL_NAME . ' VALUES(NULL, "' . $first_field . '", "' . $second_field . '", "' . $etc . '")');

Note: Of course only define table names once in a header/config file.

Tech Manager · 01-30-2008, 09:28 PM

I do professional MySQL and PHP programming. If you need some assistance please feel free to ask.

07-11-2006, 09:42 PM	#1
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Common PHP/MySQL Procedures This is how I learned to do these things; works very well and all methods are very flexible. CONNECTING TO DATABASE ---------------------------- PHP Code: `mysql_connect("localhost", "db_username", "db_pwd"); //Login as a database user. set this up in CPanel mysql_select_db("db_name"); //select the database associated with the user` INSERTING INTO A DATABASE TABLE ------------------------------------- PHP Code: `mysql_query("INSERT INTO table_name VALUES(NULL, '$first_field', '$second_field', '$etc')");` Only use that NULL if you have an auto_increment (like an 'id' field) or something. MODIFYING A DATABASE TABLE -------------------------------- PHP Code: `mysql_query("UPDATE table_name SET field_name='$variable',different_field='$anotherone' WHERE id='$thisID' LIMIT 1");` You can modify the "WHERE" clause to fit any criteria you need, and the LIMIT to whatever you want. RETRIEVING FROM A DATABASE TABLE --------------------------------------- The more complex part of database interaction... we're also going to set up the variables to USE the values we retrieve in a list, etc. PHP Code: $results = mysql_query("SELECT * FROM table_name WHERE id='$variable' LIMIT 5"); //This gets the data we want //Now we need to get and display the data, very simply. //The $row variable is an array that holds each row's data through the while loop. while ($row = mysql_fetch_array($results)) { $showThis = $row['field_name']; echo($showThis."<br>"); } //Now, each time we go through a row in the database, //(5 in this case, because of our LIMIT clause in the query), //it shows the value of a table field named "field_name" then goes //to another line for the next table row. That was the most complex part of this actually simple process. DELETING A ROW FROM A TABLE --------------------------------- PHP Code: `mysql_query("DELETE FROM table_name WHERE id='$thisID' LIMIT 1");` CLOSING DATABASE CONNETION --------------------------------- PHP Code: `mysql_close();` Questions? Examples needed? "How would I" questions? Reply... -Matt __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

07-11-2006, 10:01 PM	#2
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures hey, thanks for that, you included some info I never seen in the several tutorials I've read so far. Good info, I'll refer back to it alot. Thanks again. __________________ Storage Sheds Lost Forum

07-11-2006, 10:26 PM	#3
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures How would I...Take information in a form, pass it to the php script, and use that information with a table..okay, heres the deal. I want to enter a name into a form, and a file to upload. The name will be selected from a pre-determined drop list. So, when entering these into a new table, how do I use the table.row's primary key as part of the new filename? So.. I enter: grumpy.gif - image to upload and: oldpeople - save file as(from a drop down) submit find oldpeople entry on the directory table point to the table that contains all the oldpeople image info insert the form data in the next available row(what row number?)-this is where I need help. How do you tell what the next available row is going to be in the selected table? from there: move the image to an image folder with the name"oldpeople_$nexttablerow.$fileextension" and send that new file name to the oldpeople table Also, is there a simple way to extract the file extension so I can rename the file appropriately? __________________ Storage Sheds Lost Forum

07-12-2006, 02:06 AM	#5
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures I can verify that that's the right encryption type. The table I am accessing has a primary key thats set auto_increment. The name of the primary field is id. I am not changing the extension of the image, I want to change the file name, so I want to strip the original name out, and replace it with a common name with the tables id # concantenated at the end...like this.... thisgif_01.gif - thisgif_02.gif - thisgif_03.gif - thisgif_04.jpg and so on. Okay, so what I got so far is a form like following name: keywords: metadescription: imagename: description: In php, I want to put most of that into a table row that corresponds to the name: form field. In other words a table named $name. The name tables have all already been created, and the name: field is a drop down list. So...most of it will be easy, cause I just have to put the correct info into the correct field of the table. The hard part is changing the images original name to be $name.$idnumberintable.'.'.$originalextension. For example...I upload an image named boogy.jpg and the name field is harlem_globetrotters. The next available id# is 3, sooo....I want it to be renamed "harlem_globetrotters_03.jpg" Also, i inserted the code you gave me from above.. $var=mysql_query('SELECT BY DESC ORDER');, is there a way to echo the contents of that varialble if their isnt any records in the table yet? __________________ Storage Sheds Lost Forum

07-12-2006, 08:19 AM	#6
rohan2kool Member Join Date: Dec 2005 Posts: 132	Re: Common PHP/MySQL Procedures use var_dump() function. But as it doesn't contain any value, it will most probably be a null value. Frankly, I didn't really understand what you intended to do, but I gave my answer on the basis of whatever I did understand. __________________ Eat healthy, Stay Fit, Die Anyway. --==-- If there is a man who knows everything about women, there is a Windows distribution without bugs.

07-12-2006, 08:47 AM	#7
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures I apologize, I still am new to this, so explaining myself can be difficult. I appreciate you taking the time to dredge through my nonsense and coming up with the best answer you could. If anyone has tips as to how to explain myself better, I am all ears. __________________ Storage Sheds Lost Forum

07-12-2006, 06:22 PM	#9
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures SELECT * FROM table_name ORDER BY id DESC LIMIT 1 Sorry, I wasn't being literal. I used the exact query you gave me, I will be more specific next time. So I did all the stuff I was asking, would it help others if I posted how I did it? __________________ Storage Sheds Lost Forum

07-12-2006, 07:56 PM	#10
compuXP Retired Member Join Date: Dec 2005 Posts: 139	Re: Common PHP/MySQL Procedures In a new thread, yeah, they'd find that helpful. __________________ Retired Moderator/Member/Friend/Helper - it was great working with all of you!

07-12-2006, 08:00 PM	#11
jumpenjuhosaphat Forum Admin Contributor Join Date: Jun 2006 Location: Denver Posts: 4,459	Re: Common PHP/MySQL Procedures Okay, I will do that then. I am going to fine tune the whole script, then post it probably tonight or tommorow. __________________ Storage Sheds Lost Forum

10-14-2006, 08:17 PM	#12
pipeten Newcomer Join Date: Oct 2006 Posts: 1	Re: Common PHP/MySQL Procedures You should extend this list, seems pretty useful so far. I find print_r() one of the most useful functions (to display the contents of an array or object). __________________ http://www.pipeten.com/resellers.html

01-15-2007, 03:57 AM	#13
Tekime Smurf Join Date: Jun 2006 Location: Maine Posts: 31	Re: Common PHP/MySQL Procedures Double quotes must be parsed for variables; I diligently use single quotes so that I am in the habit of writing "faster by default". Original PHP Code: `mysql_query("INSERT INTO table_name VALUES(NULL, '$first_field', '$second_field', '$etc')");` Faster PHP Code: `mysql_query('INSERT INTO table_name VALUES(NULL, "' . $first_field . '", "' . $second_field . '", "' . $etc . '")');` I also define table names for scalability. This can save a LOT of time if an application starts to get big and muddy and you need to change a table name. PHP Code: `define('TBL_NAME', 'tbl_name'); mysql_query('INSERT INTO ' . TBL_NAME . ' VALUES(NULL, "' . $first_field . '", "' . $second_field . '", "' . $etc . '")');` Note: Of course only define table names once in a header/config file. __________________ phpLinkBid - The #1 bid for position script

01-30-2008, 09:28 PM	#14
Tech Manager Smurf Join Date: Jan 2008 Posts: 9	Re: Common PHP/MySQL Procedures I do professional MySQL and PHP programming. If you need some assistance please feel free to ask. __________________ I rely on http://www.countryipblocks.net/ for added security

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
MSSQL or MySQL?	loon	Web Hosting	3	11-20-2006 10:43 AM
Remotely Accessing MySQL Database?	compuXP	MySQL	4	08-31-2006 05:05 AM
MySql	jumpenjuhosaphat	PHP	6	07-10-2006 10:04 PM
Common domain status codes	kenni	Domain Name	1	12-31-2005 05:25 AM