Jump to content

Search the Community

Showing results for tags 'database'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome
    • Announcements & News
    • New Arrivals
  • Web Development
    • Programming
    • Database Development
    • Server Administration
    • Hosting & Domains
    • Frameworks
  • Web Design
    • HTML & CSS
    • Graphics & Multimedia
  • Desktop Discussion
    • Linux Development
    • Windows Development
    • Mac/Apple Development
    • Hardware Discussion
  • Marketing & Business
    • Advertising, Marketing, Monetization & Social Media
    • Search Engine Optimization & Traffic Building
    • Buy, Sell or Trade
  • Prodjex Web Development Applications, Tools and Plugins
    • IP.Board Applications and Plugins
    • Web Tools
  • The Developer Dump
    • General Chat

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests

Found 3 results

  1. I'm having a hard time figuring out the logic for creating a private messaging system that allows for more than 2 participants. What kind of database setup could I use to do this? I don't want to have a table with field for "user1", "user2", "user3", etc. because it strikes me as terribly inefficient and just messed up. How can I store the users of a conversation? Maybe have a table for conversations, messages, and participants? Then all the users inside the participants table that are filed under the specific conversation ID are allowed to access? Would there be a way to do it with only two tables? What do you think is the most efficient way to accomplish this task?
  2. PHP for all its sins is a very powerful tool. In terms of speed of productivity, nothing can touch it. Throw some code into a script, upload it and you are done! However this ease comes at a cost: what first appears to be "quick and easy" turns out to be an maintenance nightmare. Writing clean and robust code in PHP is hard. Often times, many newcomers do not always have a background in software engineering. This mixture of ease of use combined with no formal training in clean architecture can lead to "spaghetti code". This is one of the reasons PHP has gotten a bad reputation among professional developers. However with some effort and careful design, this does not have to be the case. In this short tutorial we shall look at writing clean and robust code that deals with databases. All web applications will have certain "settings", or "configurations". A bad approach is to copy and paste these settings all over the place! The clean approach is to place all settings and configurations into one file. This keeps things under control. When you need to make a configuration change, you no longer have to hunt you entire code base. So lets first create a "configuration" file: configuration.php: //================================== // config //================================== define("db_user", "user"); define("db_pass", "password"); define("db_name", "demo"); define("db_server", "127.0.0.1"); Now connection to a database in php is very common, however the code is quite a lot of boiler plate. Also when parameters are used for sql queries these need to be carefully "escaped" to avoid nasty security attacks such as "sql injections" By creating functions, we can encapsulate the core database access functions. This will reduce the code and also make it very clean. The sample below shows the data access functions: data_access.php: //================================== // Data Access //================================== function runSQL($Query) { //connect to server, then test for failure if(!($conn = mysql_connect(db_server,db_user,db_pass))) { print("Failed to connect to database!<br>\n"); exit(); } //select database, then test for failure if(!($dbResult = mysql_query("USE ".db_name, $conn))) { print("Can't use the database.<br>\n"); exit(); } $rs = mysql_query($Query) or die(mysql_error()); mysql_close($conn); return $rs; } function runSQLParms($Query,$Params) { //connect to server, then test for failure if(!($conn = mysql_connect(db_server,db_user,db_pass))) { print("Failed to connect to database!<br>\n"); exit(); } //select database, then test for failure if(!($dbResult = mysql_query("USE ".db_name, $conn))) { print("Can't use the database.<br>\n"); exit(); } foreach($Params as $key => $value) { $Query = str_replace($key,mysql_real_escape_string($value,$conn),$Query); } //echo $Query; $rs = mysql_query($Query) or die(mysql_error()); mysql_close(); return $rs; } In the above data access functions, we have two main functions runSQL and runSQLParms. These two functions are now ready to be used in your projects to cleanly access data. Example: Imagine we have a php file where we are required to do various database actions. First we need know we need the database "configurations" and also our database access functions. Lets import them at the start of the file: sample.php: require_once("configuration.php"); require_once("data_access.php"); Lets say we need to flush some data from a temporary table (we need to execute some sql): function FlushTemporaryData() { $sql = "delete from temp_table"; runSQL($sql); } What if we needed to delete a particular record between say a date range? We would require sql that has parameters. Usually these parameters would be values passed from client side forms. When accepting data from users, there is always a danger of "sql injections". We can safely do it using the second database access function: function DeleteUserLog($UserId, $Start_Date, $End_Date) { $sql = "delete from user_logs where userId = @UserId and log_date between @Start_Date and @End_Date"; $p["@UserId"] = $UserId; $p["@Start_Date"] = $Start_Date; $p["@End_Date"] = $End_Date; runSQLParms($sql, $p); } As you can see the runSQLParms function requires two things: The SQL query, and an array of Parameters. The function will automatically take each parameter and merge it into the SQL query. Also each parameter will be escaped to stop sql injections. Of course, we will often require to get some data back and do things with the result (such as render it as html). lets image we would like to display the top 5 students based on their score: function DisplayTopStudents() { $sql = "select First_Name, Last_Name, Score order by Score desc limit 5;"; $rs = runSQL($sql); $html = ""; while($row = mysql_fetch_array($rs)) { $html = $html . $row["First_Name"] . " " . $row["Last_Name"] . "Score:" . $row["Score"] . "<br/>"; } echo $html; } This is just the tip of the "iceberg". Hopefully you can see how easy it would be to create and maintain these functions. Questions and feedback welcome!
  3. This following will allow you to upload a .csv file via a web form then insert that data into a MYSQL database. I had a request for it this morning and so now that I have it created I figured I would share. Let me know if you see anything that would be an improvement. Step 1 - Create Web Form <form enctype="multipart/form-data" action="uploader.php" method="POST"> <input type="hidden" name="MAX_FILE_SIZE" value="100000" /> Choose a file to upload: <input name="uploadedfile" type="file" /><br /> <input type="submit" value="Upload File" /> </form> Step 2 - Create the Uploader.php File <?php // Where the file is going to be placed $target_path = "uploads/"; /* Add the original filename to our target path. Result is "uploads/filename.extension" */ $target_path = $target_path . basename( $_FILES['uploadedfile']['name']); $target_path = "uploads/"; $target_path = $target_path . basename( $_FILES['uploadedfile']['name']); if(move_uploaded_file($_FILES['uploadedfile']['tmp_name'], $target_path)) { echo "The file ". basename( $_FILES['uploadedfile']['name']). " has been uploaded"; } else{ echo "There was an error uploading the file, please try again!"; } ?> <br><a href="./import.php">Click To Import</a> Step 3 - Create the Import.php File that will insert the data into MYSQL <?php /********************************/ /* Edit the entries below to reflect the appropriate values /********************************/ $databasehost = "localhost"; $databasename = ""; $databasetable = ""; $databaseusername =""; $databasepassword = ""; $fieldseparator = ","; $lineseparator = "n"; $csvfile = "uploads/nameof.csv"; /********************************/ /* Would you like to add an ampty field at the beginning of these records? /* This is useful if you have a table with the first field being an auto_increment integer /* and the csv file does not have such as empty field before the records. /* Set 1 for yes and 0 for no. ATTENTION: don't set to 1 if you are not sure. /* This can dump data in the wrong fields if this extra field does not exist in the table /********************************/ $addauto = 0; /********************************/ /* Would you like to save the mysql queries in a file? If yes set $save to 1. /* Permission on the file should be set to 777. Either upload a sample file through ftp and /* change the permissions, or execute at the prompt: touch output.sql && chmod 777 output.sql /********************************/ $save = 0; $outputfile = "output.sql"; /********************************/ if(!file_exists($csvfile)) { echo "File not found. Make sure you specified the correct path.n"; exit; } $file = fopen($csvfile,"r"); if(!$file) { echo "Error opening data file.n"; exit; } $size = filesize($csvfile); if(!$size) { echo "File is empty.n"; exit; } $csvcontent = fread($file,$size); fclose($file); $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error()); @mysql_select_db($databasename) or die(mysql_error()); $lines = 0; $queries = ""; $linearray = array(); foreach(split($lineseparator,$csvcontent) as $line) { $lines++; $line = trim($line," t"); $line = str_replace("r","",$line); /************************************ This line escapes the special character. remove it if entries are already escaped in the csv file ************************************/ $line = str_replace("'","'",$line); /*************************************/ $linearray = explode($fieldseparator,$line); $linemysql = implode("','",$linearray); if($addauto) $query = "insert into $databasetable values('','$linemysql');"; else $query = "insert into $databasetable values('$linemysql');"; $queries .= $query . "n"; @mysql_query($query); } @mysql_close($con); if($save) { if(!is_writable($outputfile)) { echo "File is not writable, check permissions.n"; } else { $file2 = fopen($outputfile,"w"); if(!$file2) { echo "Error writing to the output file.n"; } else { fwrite($file2,$queries); fclose($file2); } } } echo "Found a total of $lines records in this csv file.n"; ?> For this to work correctly all you need to do is put all 3 of these .php files in the same directory and create another directory within called "uploads".
×