|
PHP - Datatypes For Different Fields In A Database
I am quite new to PHP and MySQL. I am trying to figure out what datatypes to use for different fields in a Database. For ID (Identification) I use INT, for name I use VARCHAR and for people to write text messages, I use LONGTEXT. For date and time I use DATETIME.
What I am unsure about, however, is what type to use for an email adress and for an IP address? I have been watching videos at Youtube about the Datatype for IP addresses, but they don't seem always to agree. I hope someone can put me in the right direction on those two.
Regards,
Erik
Similar TutorialsHello, My current server setup :
PHP version: 7.1.4
From what I read, in order to have MySQL return native datatypes (as opposed to just strings) : Now, I do have mysqlnd, but a simple PDO Prepared statement still returns me all strings, even though some columns are set as INT, VARCHAR, DATETIME, etc..
$stmt = $pdo->prepare("SELECT * FROM tbl_users WHERE id=1");
$stmt->execute();
$user = $stmt->fetch();
var_dump($user);
OUTPUT:
array(13) {
["id"]=>
string(1) "1"
["is_admin"]=>
string(1) "1"
["is_active"]=>
string(1) "1"
["username"]=>
string(5) "jdoe"
...
}
I'm not sure what to look for to resolve this issue if I want it to return INT as INT and not STRING, etc. Does having `extension_loaded('mysqlnd') == true`, make my system automatically use this native mysql driver? How can I tell ? Could this be a version problem perhaps?
Much thanks! Pat
Hi can anybody help I need to export and download tables from a database into a excel sheet. I have this code and it works what I need is to export specific fields and not just the whole table can anyone help modifying the code to export certain fields within the table please? Here is the code... Code: [Select] <?php $host = 'localhost'; $user = 'user'; $pass = 'password'; $db = 'qdbname'; $table = 'tablename'; $file = 'export'; $link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error()); mysql_select_db($db) or die("Can not connect."); $result = mysql_query("SHOW COLUMNS FROM ".$table.""); $i = 0; if (mysql_num_rows($result) > 0) { while ($row = mysql_fetch_assoc($result)) { $csv_output .= $row['Field']."; "; $i++; } } $csv_output .= "\n"; $values = mysql_query("SELECT * FROM ".$table.""); while ($rowr = mysql_fetch_row($values)) { for ($j=0;$j<$i;$j++) { $csv_output .= $rowr[$j]."; "; } $csv_output .= "\n"; } $filename = $file."_".date("Y-m-d_H-i",time()); header("Content-type: application/vnd.ms-excel"); header("Content-disposition: csv" . date("Y-m-d") . ".csv"); header( "Content-disposition: filename=".$filename.".csv"); print $csv_output; exit; ?> Hi all, I'm brand new to php / mysql and I'm trying to modify this script which currently runs a search on two fields "product_number" and "product_name". I also want the search to consider a third field "product_type". When I remove either product_name or product number from the following part of the code, it removes that field from being considered in the search: Code: [Select] $search_map = array($orderby, 'product_number','product_name',); So shouldn't I be able to just add the third field's name to this array and have it be considered too? I'm a complete beginner so I'm hoping this is a simple fix and I just dont get it. Here's the rest of what I think is the relevant code: Code: [Select] function load_all_products() { global $AppUI; global $sorted_item_list; global $additional_pfilter; load_type_list(); $orderby = 'product_id'; $search_map = array($orderby, 'product_number','product_name',); $where = $AppUI->getState( 'QuoteIdxWhere' ) ? $AppUI->getState( 'QuoteIdxWhere' ) : '*'; $not =" "; $op = " OR"; $check = substr(trim($where),0,4); if(stristr($check,"NOT")){ $op ="AND"; $not = " NOT "; $where = substr(trim($where),4); $where = trim($where); } // assemble the sql statement $q = new DBQuery; $q->addTable('products'); $q->addJoin('companies', 'com', 'products.product_company_id = com.company_id'); $q->addJoin('users', 'u', 'products.product_owner = u.user_id'); $q->addQuery('products.*, com.company_name, u.user_username'); $where_filter = " "; foreach($search_map as $search_name) $where_filter .=" $op $search_name $not REGEXP '$where'"; $where_filter = substr($where_filter, 5); // echo $where_filter; if($where != "*") $q->addWhere("($where_filter)"); $q->addOrder('product_id'); $sql = $q->prepare(); $q->clear(); $sql_list = db_exec( $sql ); if ($sql_list) $rn = db_num_rows( $sql_list ); else { echo db_error(); $rn = 0; } $product_list = array(); foreach ($sql_list as $item) { $product_list[ $item[ 'product_id' ] ] = $item; } // sort the list global $sort_state; if ( !isset( $sort_state ) ) $sort_state = getProductSortState(); if ( isset( $sort_state[ 'sort_item1' ] ) ) { if ( isset( $sort_state[ 'sort_item2' ] ) ) { $sorted_item_list = array_csort2( $product_list, $sort_state['sort_item1'], intval( $sort_state['sort_order1'] ) , $sort_state[ 'sort_item2' ], intval( $sort_state['sort_order2'] ) ); } else { $sorted_item_list = array_csort2( $product_list, $sort_state['sort_item1'], intval($sort_state['sort_order1']) ); } } else $sorted_item_list = array_csort2( $product_list, 'product_id' ); } Thanks! Hiya I was wondering how one would get make dynamic url variable value pairs from a databases fields. I have a database that I want to use to store variable value pairs that will be used on my pages for dynamic content. It will check one thing for certain besides if the variable value pair match. The table looks like this rpg loc_type loc_type template I want it to check if the rpg is in an array and then check if the url variable and value pair match on from the database. I dont know much about auto code that would generate this on its own and I'm not sure if you can say $_GET[$location]; I dont want to hard code it so it only works with what i code I want the urls variable value pair to be based on the database info which determines which template to show based on those values. I wrote a piece of code that lists images that don't have a match in the database: Code: [Select] $dir_path = '../images/productImages'; $images = scandir($dir_path); $query = "SELECT image FROM Database_table ORDER BY name DESC"; $results = mysql_query($query); $output = "<h1>Missing Images</h1>"; $output .= "<div class=\"missingImages\">"; $output .= "<p>The following data was found in the Database_table.images field but not in /images/productImages:</p>"; $output .= "<div class=\"missingImg\">"; $output .= "<p class=\"fieldName\">Database_table.images:</p>"; while ($fetch = mysql_fetch_array($results)) { $clean_fetch = str_replace("productImages/", "", $fetch); $diff = array_diff($clean_fetch, $images); foreach (array_unique($diff) as $key => $value) { $output .= $value . "<br />"; } } $output .= "</div>"; $output .= "</div>"; echo $output; Works. Now I would like to check whether there are any files in the folder that are not in the database table. I thought it would be as easy as swapping the array_diff parameters around but it's not. When i swap the parameters like so - array_diff($images, $clean_fetch) it gives me a list of ALMOST all the images in the image folder, which i don't understand. Why does it not give me only the images that are present in the folder but not in the database? hi every body.
i want a loop that takes and shows more than one record from database.
and then i want to show these to user by ajax.
indeed i made an html page that when user arrives at end it must do this.
first i made loop that took one record from database and it worked fine.
but for taking several records it went wrong.
this is my html file that has interact by user
webgoo-ajax.html 4.36KB
2 downloads
and this is my php file that interacts by database
ajax-get.php 1012bytes
3 downloads
when user arrives at end of page naturally ajax must be run and show several records to user.but it does'nt.
first it stays idle for 3 minutes and then loads my first record extremely.
and a point: if these pages does'nt work on IE it will work on Firefox because it did'nt worked in IE for me too.
i just want a for loop that work correctly for my need.
thank you for everything
This topic has been moved to Ajax Help. http://www.phpfreaks.com/forums/index.php?topic=352528.0 I have a relational table call sales with prodcut_id and custmer_id tables. I also have a product and customer tables. products table with product_id as an auto increment and customer with custumer_id I want to join through the sales tables all t he fields of row 1 from tables customer and products and pull it at once. This is a member login script and I want to display the products by members. So far I have this query to display the products once the member is login in. $userid is the id of the customer coming from the $userid= $_SESSION['customer_id']; $mysqlSales="SELECT products.* FROM products JOIN sales ON (products.product_id = procuct_id ) WHERE sales.customer_id = '$userid'"; so far that statement is not working where should I have some type of incoherance with the english statement above expressing what I want the query to do. Hi friends, a quick print_r() shows the following array while connecting to my database. How to get them as individual items and display on the website ? Array ( [0] => Array ( [id] => 52 [document_name] => xyz [document_ext] => gif [download_file_name] => ktm_impact.gif [upload_dt] => 2010-08-11 ) ) I'm using PHP and MySQL. I have an application where I'd like to keep certain database (character) fields encrypted when added to the database and stored, but when I need to display them at the appropriate time, I can call a function and quickly decrypt them for display. Does anyone know of a simple way to do this? Thanks! I can not get the values from the javascript add row to go dynamically as a row into MySql only the form values show up as the form below as one row. I made it as an array, but no such luck, I have tried this code around a multitude of ways. I don't know what I am doing wrong, kindly write out the correct way.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR...ransitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Dynamic Fields js/php to MySql need to submit dynamically to the database</title> <?php require ('database.php'); ?> <script type="text/javascript"> var counter = 1; var collector = ""; function addfields(indx) { var tbl = document.getElementById('table_id'); var newtr = document.createElement('tr'); counter = counter + indx; newtr.setAttribute('id','tr'+counter); newtr.innerHTML = '<td><input type="checkbox" name="checkb'+counter+'" id="checkb'+counter+'" value="'+counter+'" onclick="checkme('+counter+')"></td><td><input type="text" name="text1[]"></td><td><textarea name="textarea1[]"></textarea></td>'; tbl.appendChild(newtr); } function checkme(dx) { collector += dx+","; } function deletetherow(indx) { var col = collector.split(","); for (var i = 0; i < col.length; i++) { var remvelem = document.getElementById('tr'+col[i]); var chckbx = document.getElementById("checkb"+col[i]); if(remvelem && chckbx.checked) { var tbl = document.getElementById('table_id'); tbl.removeChild(remvelem); } } } </script> </head> <body> <form enctype="multipart/form-data" id="1" style="background-color:#ffffff;" action="<?php echo $_SERVER['PHP_SELF']; ?>"></form> <table id="table_id" > <tr id="tr1" class="trmain"> <td> </td> <td> <input type="text" name="text1[]"> </td> <td> <textarea name="textarea1[]"></textarea> </td> </tr> </table> <input type="button" value="Add" onClick="addfields(1);" /> <input type="button" value="Delete" onClick="deletetherow()" /> <input type="submit" value="Send" id="submit" name="submit"/> <?php if(isset($_POST['submit'])) { for ($i=0; $i < count($_POST['text1']); $i++ ) { $ced = stripslashes($_POST['text1'][$i]); $erg = stripslashes($_POST['textarea1'][$i]); } $bnt = mysql_query("INSERT INTO tablename (first, second) VALUES ('$ced', '$erg')")or die('Error: '. mysql_error() ); $result = mysql_query($bnt); } ?> </body> </html> I am currently creating a form and I want to populate a drop down selection menu with data from two fields in a form. For example, I want it to pull the first and last name fields from a database to populate names in a drop down menu in a form. I want the form to submit to the email address of the person selected in the drop down. Is this possible to do? The email is already a field in the record of the person in the database. Can anyone give me some pointers or advice on how I should go about setting up the "Select" box drop down? I am not sure how to code it to do what I am wanting. Any links to relevant help would be appreciated too. Thanks in advance! Hey guys,
Thank you in advance... here is my situation, I have a form with three (3) fields in it, the 'student name' is unlimited textfield with an "add more" button to it and I have two select fields ('number of shirts' and 'trophies') that depend on the number of entries for 'student name'...
I want to create the select fields based on this math, for as many 'student name' entries:
1- i want to have the select form for 'number of shirts' to be 0 up to that number... so if there are 6 'student name' entries, the select options will be 0,1,3,4,5,6,7
2- I want to have the select form for 'trophies' to be 5 'student name' entries to 1 'trophies', for example if there are 6 'student name' entries, the select options will be 0,1... if there are 13 entries, options will be 0,1,2... So if there are less than 5 'student name' entries, the select field will not show (hidden)
of course if there are no 'student name' entries, these two fields won't show up (hidden)
let me know if that make sense and ANY help or directions will be GREATLY APPRECIATED.
Thanks guys!
hello everyone, I am about to start coding my pages to display results from a database but before i do i want to know information about the following : Is it best to upload images to a database?and display them accordingly? or is it best to use images from a directory? What is most commonly used and or more reliable? Another topic i have trouble finding information on is actually positioning the output from you mysql database, is this practice done with tables?fields and rows? What is this method called? And is there more then one way to go about controlling result layout on your page? Sorry about the 1001 questions , but i am unable to find a clear answer on the topic ..especially question two . Thanks in advance. I have a very tricky php problem here. At least for me. On a page that is editing a job entry. I have a database generated group of checkboxes some of which have been checked when the job was entered. So the script has to do two things generate the complete list of checkboxes and check the ones that have already been selected in the job request. The below script is what I've done trying to figure it out. Description of what I'm trying to do: I'm generating the the boxes with a call to my database for the list of checkboxes. Then I'm building an array with all the possible items that could be checked to compare against. Ok here is where I think my first problem is. I need to make a second query to another table "worklog" in the same database. This is where the list of checked items are stored. What is the best way to do this? A second query seems wrong "or at least not efficient" Code: [Select] <div class="text">Job Type (<a class="editlist" href="javascript:editlist('listedit.php?edit=typelist',700,400);">edit list</a>):</div> <div class="field"> <?PHP $connection=mysql_connect ("localhost", "user", "password") or die ("I cannot connect to the database."); $db=mysql_select_db ("database", $connection) or die (mysql_error()); $query = "SELECT type FROM typelist ORDER BY type ASC"; $sql_result = mysql_query($query, $connection) or die (mysql_error()); $i=1; echo '<table valign="top"><tr><td>'; while ($row = mysql_fetch_array($sql_result)) { $type = $row["type"]; if ($i > 1 && $i % 26 == 0) echo '</td><td>'; else if ($i) echo ''; ++$i; $aTypes = array ("Spec Ad Campaign", "100 x 100 Logo", "100 x 35 (Featured Developer/Broker)", "100 x 40 (Featured Lender)", "120 x 180 (Home Page Auto)", "120 x 45 (Half Tile - Jobs)", "120x60 (Section Sponsor)", "125 x 40 (Profile Page logo)", "135 x 31 (Autos)", "135 x 60 (Autos)", "135 x 60 (Logotile - Jobs)", "150 x 40 (Auto Logo)", "160 x 240 (monster tile)", "160 x 400 (Skyscraper)", "160 x 600 (Tower)", "170x30 (Section Sponsor)", "240 x 180 JPG/GIF Auto Video", "240 x 180 size FLV video ", "300 x 250 (Story Ad)", "300 x 600 (Halfpage)", "468 x 60 (Banner Jpg/Gif)", "728 x 90 (Leaderboard)", "95 x 75 (Sweeps Logo) ", "Agent Profile Page", "Contest", "Creative Change", "Employer Profile", "Holiday Shop", "Home of the Week", "HP Half Banner (234 x 60 Static)", "Mobile Ads (320x53 *300x50*216x36*168x28)", "Newsletter", "Peelback", "Pencil Ad", "Print", "Real Deals", "Resize ad campaign", "Roll-Over Skyscraper", "Site Sponsor logo (170x30)", "Splash Page Production", "Travel Deals Update", "Video Ad", "Web Development", "Web Maintenance", "Web Quote (or Design)"); $dbTypes = explode(',',$row['type']); foreach ($aTypes as $type){ if(in_array($aType,$dbTypes)){ echo "<input style='font-size:10px;' name='type[]' type='checkbox' value='$type' CHECKED><span style='color:#000;'>$type</span></input><br/>"; }else { echo "<input style='font-size:10px;' name='type[]' type='checkbox' value='$type'><span style='color:#000;'>$type</span></input><br/>"; } } } echo '</td></tr></table>'; ?> </div> At the moment I am creating a search function for my website. The approach I have in mind is a pseudo-PHP database. To give an example: A HTML form will submit the results to a PHP file. HTML FORM - Colour: Black PHP RESULT PAGE - if ($_POST['color'] == 'Black') {readfile("./products/black/*.html");} HTML FORM - Price: <$50 PHP RESULT PAGE - if ($_POST['Price'] == '<$50') {readfile("./products/less50/*.html");} The problem here is if there is an item that is black and costs less than $50, then its going to be listed twice. There is probably some code I can write to ommit the listing of duplicate entries, but it is probably going to be messy, so I am wondering if its better to use a centralized MySQL database, rather than a pseudo-PHP database? I've never used MySQL and don't know much about it and this is my first real attempt at using PHP. after cloasing connection of database i still got the values form database. Code: [Select] <?php session_start(); /* * To change this template, choose Tools | Templates * and open the template in the editor. */ require_once '../database/db_connecting.php'; $dbname="sahansevena";//set database name $con= setConnections();//make connections use implemented methode in db_connectiong.php mysql_select_db($dbname, $con); //update the time and date of the admin table $update_time="update admin set last_logged_date =CURDATE(), last_log_time=CURTIME() where username='$uname'limit 3,4"; //my admin table contain 5 colums they are id, username,password, last_logged_date, last_log_time $link= mysql_query($update_time); // mysql_select_db($dbname, $link); //$con=mysql_connect('localhost', 'root','ijts'); $result="select * from admin where username='a'"; $result=mysql_query($result); mysql_close($con); //here i just check after closing data baseconnection whether i do get reselts but i do, why? echo "after the cnnection was closed"; if(!$result){ echo "cont fetch data"; }else{ $row= mysql_fetch_array($result); echo "id".$row[0]."usrname".$row[1]."passwped".$row[2]."date".$row[3]."time".$row[4]; } // echo "<html>"; //echo "<table border='1' cellspacing='1' cellpadding='2' align='center'>"; // echo "<thead>"; // echo"<tr>"; // echo "<th>"; // echo ID; // echo"</th>"; // echo" <th>";echo Username; echo"</th>"; // echo"<th>";echo Password; echo"</th>"; // echo"<th>";echo Last_logged_date; echo "</th>"; // echo "<th>";echo Last_logged_time; echo "</th>"; // echo" </tr>"; // echo" </thead>"; // echo" <tbody>"; //while($row= mysql_fetch_array($result,MYSQL_BOTH)){ // echo "<tr>"; // echo "<td>"; // echo $row[0]; // echo "</td>"; // echo "<td>"; // echo $row[1]; // echo "</td>"; // echo "<td>"; // echo $row[2]; // echo "</td>"; // echo "<td>"; // echo $row[3]; // echo "</td>"; // echo "<td>"; // echo $row[4]; // echo "</td>"; // echo "</tr>"; // } // echo" </tbody>"; // echo "</table>"; // echo "</html>"; session_destroy(); session_commit(); echo "session and database are closed but i still get values from doatabase session is destroyed".$_SESSION['admin']; ?> session is destroyed but database connection is not closed. thanks I have a standard form that displays users current data from a mysql database once logged in(code obtained from the internet). Users can then edit their data then submit it to page called editform.php that does the update. All works well except that the page does not display the updated info. Users have to first logout and login again to see the updated info. even refreshing the page does not show the new info. Please tell me where the problem is as i am new to php.
my form page test.php
<?PHP
require_once("./include/membersite_config.php");
if(!$fgmembersite->CheckLogin())
{
$fgmembersite->RedirectToURL("login.php");
exit;
}
?>
<form action="editform.php?id_user=<?= $fgmembersite->UserId() ?>" method="POST">
<input type="hidden" name="id_user" value="<?= $fgmembersite->UserId() ?>"><br>
Name:<br> <input type="text" name="name" size="40" value="<?= $fgmembersite->UserFullName() ?>"><br><br>
Email:<br> <input type="text" name="email" size="40" value="<?= $fgmembersite->UserEmail() ?> "><br><br>
Address:<br> <input type="text" name="address" size="40" value="<?= $fgmembersite->UserAddress() ?> "><br><br>
<button>Submit</button>
my editform.php
<?php
$con = mysqli_connect("localhost","root","user","pass");
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
mysqli_query($con,"UPDATE fgusers3 SET name = '".$_POST['name']."', email= '".$_POST['email']."', address= '".$_POST['address']."' WHERE id_user='".$_POST['id_user']."'");
header("Location: test.php");
?>
Hi guys, I was just wondering if anyone could help me. I've got a My_SQL database containing articles, a summary for the article and a date. I have a basic CMS system set-up, but I want to create a script that when users sign up to a mail list it forwards the summary and dates of the articles database. If that makes sense? But I only want it to forward the most recent 5 rows. I'm pretty new to PHP and I've been mostly following tutorials thus far, but this is quite specific. Thanks in advance! hello I want query from one table and insert in another table on another domain . each database on one domain name. for example http://www.site.com $con1 and http://www.site1.com $con. can anyone help me? my code is : <?php $dbuser1 = "insert in this database"; $dbpass1 = "insert in this database"; $dbhost1 = "localhost"; $dbname1 = "insert in this database"; // Connecting, selecting database $con1 = mysql_connect($dbhost1, $dbuser1, $dbpass1) or die('Could not connect: ' . mysql_error()); mysql_select_db($dbname1) or die('Could not select database'); $dbuser = "query from this database"; $dbpass = "query from this database"; $dbhost = "localhost"; $dbname = "query from this database"; // Connecting, selecting database $con = mysql_connect($dbhost, $dbuser, $dbpass) or die('Could not connect: ' . mysql_error()); mysql_select_db($dbname) or die('Could not select database'); //query from database $query = mysql_query("SELECT * FROM `second_content` WHERE CHANGED =0 limit 0,1"); while($row=mysql_fetch_array($query)){ $result=$row[0]; $text=$row[1]."</br>Size:(".$row[4].")"; $alias=$row[2]; $link = '<a target="_blank" href='.$row[3].'>Download</a>'; echo $result; } //insert into database mysql_query("SET NAMES 'utf8'", $con1); $query3= " INSERT INTO `jos_content` (`id`, `title`, `alias`, `) VALUES (NULL, '".$result."', '".$alias."', '')"; if (!mysql_query($query3,$con1)) { die('Error: text add' . mysql_error()); } mysql_close($con); mysql_close($con1); ?> |
|||||||