?
Solved

Need PHP function for sql database

Posted on 2011-09-07
8
Medium Priority
?
310 Views
Last Modified: 2012-05-12
Hi - I'm a newbie to PHP.
I need a function to access an sql database.
I'm using the following code which is very messy and probably unsafe, but definitely a waste as it's repeated so many times (but it does work):
Search.php -
  $db_name = "db.Questions";  
  include "connect.php";
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();

and the include (connect.php) has -
<?php
  $ctr=0;
  $errmsg = "";
  $db_host = "localhost";
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
?>  

How do I get it all into connect.php?
0
Comment
Question by:dcass
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
8 Comments
 
LVL 2

Expert Comment

by:shdwmage
ID: 36499964
Wow, what a mess!

Search.php - 
  $db_name = "db.Questions";   
  include "connect.php";
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();

Open in new window


<?php
  $ctr=0;
  $errmsg = "";
  $db_host = "localhost"; 
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
?>  

Open in new window


Sorry it's a lot easier to read in the code boxes as I can use highlighting.

So if I understand you correctly you just want to put all of this SQL in one file?
0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500013
I guess the best way to help you would be to know what exactly you are looking for as far as results.

When I make a database connection I use a db_connect file that I use as an include on each page.
<?php 
DEFINE ('DB_HOST', 'localhost');
DEFINE ('DB_NAME', 'db');
DEFINE ('DB_USER', 'public');
DEFINE ('DB_PASSWORD', 'password');

//make the connection
$dbc = @mysql_connect(DB_HOST, DB_USER, DB_PASSWORD) or die("Couldn't connect to MySQL");

//select the database
@mysql_select_db(DB_NAME) or die("Couldn't open $db: ".mysql_error());

?>

Open in new window


I always try to keep my connection information separate from my queries because I can re-use the file in many different applications with a few changes, like username and password.

But if you really wanted to merge them into connect.php all you would need to do is replace the include "connect.php" part in search.php with the code in connect.php

<?php
  $db_name = "db.Questions";   
$ctr=0;
  $errmsg = "";
  $db_host = "localhost"; 
  $db_user = "user";
  $db_word = "word";
  $link = mysql_connect($db_host, $db_user, $db_word);

  if (!$link) {
    die('Could not connect: ' . mysql_error());
  }
  $db = "db";
  if (!mysql_select_db($db)) {
    die('Could not select database: ' . mysql_error());
  }
  mysql_select_db($db, $link);
  $sql = "select * from db.Questions where var0='".$Survey."' order by var3";
  $res = mysql_query($sql);
  $ct = mysql_num_rows($res);
  mysql_close();
?>

Open in new window

0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500044
Additionally I would just like to explain what an include does.

When you use an include you are taking the content of the file you have written and merging all of the data of it into the current file.  

This allows you to "modularize" your code, making future updates and changes easier by allowing you to use the same chunk of code in many files.  It saves space on your server, and saves a lot of duplicate code to boot.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:dcass
ID: 36500282
I do understand include files - I'm new to php but not asp - I've used that for years.
What I need is :
1) to make sure that my connection info is secure
2) to pass the info, including the sql statement, into a function and bring back the results
OR
is what I'm doing OK?
What is the standard way of accessing a database in PHP?
0
 
LVL 2

Accepted Solution

by:
shdwmage earned 2000 total points
ID: 36500319
I normally store the MySQL data below the public_html directory into a separate folder and use the file system to bring up the data.  It will prevent easy access to the information.

Additionally I have a function that I call clean_input() that will scrub all user input data for a whole bunch of crap.  I could probably add a lot more error validation to this but here is what I use:

#2) Clean input - verify that input data has no malicous intent.
function clean_input($alpha){
	#check and see if data has actually been input
	if (isset($alpha)){
		$cleaned = htmlentities($alpha);
		$cleaned = stripslashes($cleaned);
		$cleaned = addslashes($cleaned);
		$cleaned = trim($cleaned);
		$cleaned = strip_tags($cleaned);
	#if not set it to blank and ignore it.
	} else {
		$cleaned = NULL;
	}
	#return the data to the main page
	return $cleaned;
};

Open in new window

0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36500325
I then just use that function to check all user input data.
0
 

Author Comment

by:dcass
ID: 36500389
So, just build a text file with the login info and pull it up when I need it?  Then do the work (select, update, etc.) in the actual php pgm?

Thanks for the cleaner - I needed that too!
0
 
LVL 2

Expert Comment

by:shdwmage
ID: 36501776
I normally use a php file, but txt or even an ini file would work as well.

For security purposes none of my web-accessible users have access to the delete features.  If I need to delete a record I hide it by setting a flag (field on it) and then I just filter based on that field and not show the things flagged for deletion.

Normally I will have 3 different sets of MySQL users with differing permissions.

User - one that can just access and view the data. This is handy for pages that load from the database, but you want to reduce the surface for a SQL attack

Power User - I use this for areas where I have decided to give people access to change the database.  They have permissions to update, insert, etc just not delete.

Administrator - this is the account I use to administer the database, it has all the permissions but it isn't tied anywhere to the web world.

Sometimes i will have a fourth account, a counter type account.
counter-  this account only has the insert permission and it will insert things into a database table like IP, session info, etc. This allows me to track data where otherwise I wouldn't the person to be able to put things into my database.

Now I actually have all of these in one file and use a switch statement to figure out which one of them to use.

switch ($permissions) {
   case user:
        DEFINE ('DB_USER', 'public');
        DEFINE ('DB_PASSWORD', 'password');
       break;
    case poweruser:
        define some stuff here
     break;
     default:
          define some stuff here
     break;
};

Finish with the rest of the information.

Open in new window


That allows me to put more than one chunk of code in the same file.  In the file that calls the connection file I would predefine $permissions with what user I wanted it to use.  In case I forgot to do it is why the default is set.  Which in mine is normally the user permissions.

That is just a way to avoid file bloating and keeping all of your database connection information in one file.

Hope that helps, and glad I could help you alleviate your issues!
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question