MySQL multiple NOT LIKE query

Here is multiple NOT LIKE MySQL query with webmail services email ids are excluded

SELECT * FROM promo123 WHERE cemail NOT LIKE '%gmail%'AND cemail NOT LIKE '%yahoo%' AND cemail NOT LIKE '%hotmail%' AND cemail NOT LIKE '%yahoomail%' AND cemail NOT LIKE "%aol%" AND cemail NOT LIKE "%icloud%" AND cemail NOT LIKE "%me.com%" AND cemail NOT LIKE "%live.com%" AND cemail NOT LIKE "%outlook.com%" AND cemail !=""

XAMPP MySQL Blocked Port [SOLVED]

Deleting this file is like playing a Russian roulette with your databases, it could work and restablish everything, but also, probably could leave unusable every database you have.

Instead, first try using the MySQL backup folder which is included with XAMPP. So do the next:

• Rename the folder mysql/data to mysql/data_old (you can use any name)
• Create a new folder mysql/data
• Copy the content that resides in mysql/backup to the new mysql/data folder
• Copy all your database folders that are in mysql/data_old to mysql/data (skipping the mysql, performance_schema, and phpmyadmin folders from data_old)
• Finally copy the ibdata1 file from mysql/data_old and replace it inside mysql/data folder
• Start MySQL from XAMPP control panel

And, voilà!

MLM Hierarchical TreeView MySQL PHP [solved]

Multi-Level Marketing Members Hierarchical Tree View in MySQL and PHP for your references

MySQL Table Structure

create table mlm_members(id int auto_increment primary key, parentID int, member_name varchar(50))

PHP MLM Treeview Code Reference

<? include('config.php'); function display_children($parent, $level, $con) { $count = 0; $result = mysqli_query($con,'select id,parentID,mname from mlm_members where parentID="' . $parent . '"'); echo '<ul id="xul">'; while($row = mysqli_fetch_array($result)){ $var = $row["member_name"] . '<br>' . str_repeat(' ',$level).$row["id"] . '<br>'; echo $var; $count += display_children($row["id"],$level + 1,$con); } echo '</ul>'; } display_children(102,0,$con); ?>

AJAX JSON response from PHP MySQL rows to html table

After lots of banging head and stackoverflow here is how to displaly AJAX JSON response from PHP MySQL to html table for your referenses

RESPONSE.PHP ---- get user query json response from PHP MySQL

<?php include('config.php'); $rno = $_POST["yno"]; //$rno = '215412141'; $mst = mysqli_query($con,'select * from usermst where uno="' . $rno . '"'); $rst = mysqli_fetch_assoc($mst); $ucode = $rst["ucode"]; $result = mysqli_query($con,'select uno,ucode,uref,uname,uphone,uemail from usermst where uref="' . $ucode . '"'); $rows = array(); while($r = mysqli_fetch_assoc($result)){ $rows[] = array('data' => $r); } echo json_encode($rows); ?>

AJAX Response Data Retrival Page

<script> $( function() { var availableTags = [ <?php $ukd = mysqli_query($con,'select uname,uno,ucode from usermst order by uno ASC') or die('usermst retrival error : ' . mysqli_error($con)); while($kref = mysqli_fetch_assoc($ukd)){ ?> "<?php echo $kref['uno'] . ' - ' . $kref['uname']; ?>", <?php } ?> ]; $( "#uref" ).autocomplete({ source: availableTags }); } ); function getRef(){ var xtr = document.getElementById("uref").value; var xref = xtr.split(" - "); $.ajax({ url: 'getref.php', type: 'post', data: {yno:xref[0]}, // crossDomain: true, dataType: 'JSON', success: function(response){ console.log(response); var len = response.length; if(len == null){ for(var i=0; i<len; i++){ var id = response[i].data.uno; var icode = response[i].data.ucode; var name = response[i].data.uname; var tr_str = "<tr>" + "<td align='center'>" + (i+1) + "</td>" + "<td align='center'>" + id + "</td>" + "<td align='center'>" + icode + "</td>" + "<td align='center'>" + name + "</td>" + "</tr>"; $("#records_table tbody").append(tr_str); } } else { $('#records_table tbody').after('<tr><td colspan="4">No Records Found</td></tr>'); } } }); } </script>

encode multiple rows to json in php mysql

how to encode multiple rows from mysql table to php encode_json?

very simple!

$result = mysqli_query($con,'select uno,ucode,uref,uname,uphone,uemail from usermst order by uname ASC'); $rows = array(); while($r = mysqli_fetch_assoc($result)){ $rows[] = array('data' => $r); } echo json_encode($rows);

MYSQL - Group by date and Sum by Everyday

If want to calculate sum of everyday here is simple ways to do it

Sum of Everyday Descending Order in MySQL

select date(csdate) as xdate, sum(ifare) as total from rform group by date(csdate) order by date(csdate) DESC

Sum of A Day in Mysql

select csdate, ifare from rform where date(csdate) = "2015-09-21"

Removing white space non-numeric characters parenthesis spaces hyphens of Phone Number formatting in MySQL Query Statement

This is God of All Small Things.

You have phone number with too many formattting like
(444) 444-4444
(555)555-5555
800-123-4567
877 987-4563

and you want to QUERY it in MySQL like "4085439351" --- you will have to write a trigger and use it as function in your MYSQL QUERY STATEMENT

STEP#1 -- WRITE A QUERY

// phone10() is your function name DELIMITER $$ USE [database]$$ // USE A DATABASE WHERE YOU WANT TO SAVE YOUR TRIGGER DROP FUNCTION IF EXISTS `phone10`$$ CREATE DEFINER=`root`@`localhost` FUNCTION `phone10`(str VARCHAR(1000)) RETURNS VARCHAR(1000) CHARSET latin1 DETERMINISTIC BEGIN DECLARE counter INT DEFAULT 0; DECLARE strLength INT DEFAULT 0; DECLARE strChar VARCHAR(1000) DEFAULT '' ; DECLARE retVal VARCHAR(1000) DEFAULT ''; SET strLength = LENGTH(str); WHILE strLength > 0 DO SET counter = counter+1; SET strChar = SUBSTRING(str,counter,1); IF strChar REGEXP('[0-9]+') = 1 THEN SET retVal = CONCAT(retVal,strChar); END IF; SET strLength = strLength -1; SET strChar = NULL; END WHILE; RETURN retVal; END $$ DELIMITER ;

MYSQL QUERY STATEMENT

SELECT person FROM usermst WHERE phone10(phone_number) = '123456789' select phone10(phone_number) from usermst

SIMPLE - ISN'T IT???

PHP MySQL multiple rows to JSON

Simple to store PHP MySQL to JSON with multiple rows result

<?php include('config.php'); $mkt = mysqli_query($con,'select * from booking order by csdate DESC'); //while //$mkt = mysqli_query($con, $sql); $result = array("booknow" => array()); //create an associative array with a property called "dashboard_content", which is an array. (json_encode will convert an associative array to a JSON object) if (check) { $rows = mysqli_fetch_assoc($mkt); if (!$rows) { echo "No results!"; } else { while ($rows = mysqli_fetch_assoc($mkt)) { $pickup = $rows['pickup']; $drop = $rows['drop']; $csdate = date('m-d-Y @ h:i A', strtotime($rows['csdate'])); $cmsgs = $rows['cmsgs']; $tstamp = $rows['tstamp']; // $rstatus = //append the current data to a new entry in the "dashboard_content" array $result["booknow"][] = array('pickup' => $cadd, 'drop' => $drop, 'csdate' => $csdate, 'cmsgs' => $cmsgs, 'tstamp' => $tstamp); } } } //now, output the whole completed result to one single, coherent, valid JSON array. echo json_encode($result); ?>

HASH Algorithm in MySQL PHP Query - SOLVED

MySQL Query of HASH SHA2 Type of SHA384

select * from users where SHA2(userid,384)="839121c5bbda9f7b630137aa10104cae53085f7a8a030d7b37f8158318ba20d059276c7aa0c455bdba3937582160a0b8"

PHP CONVERSION into HASH SHA2 Type of SHA384

<?php hash( 'sha256', '789889 ); ?>

List of universal HASH types for your references and MD5 is most common used - which can be easily DECODE by hash machines in seconds.

Array ( [0] => md2 [1] => md4 [2] => md5 [3] => sha1 [4] => sha224 [5] => sha256 [6] => sha384 [7] => sha512 [8] => ripemd128 [9] => ripemd160 [10] => ripemd256 [11] => ripemd320 [12] => whirlpool [13] => tiger128,3 [14] => tiger160,3 [15] => tiger192,3 [16] => tiger128,4 [17] => tiger160,4 [18] => tiger192,4 [19] => snefru [20] => snefru256 [21] => gost [22] => gost-crypto [23] => adler32 [24] => crc32 [25] => crc32b [26] => fnv132 [27] => fnv1a32 [28] => fnv164 [29] => fnv1a64 [30] => joaat [31] => haval128,3 [32] => haval160,3 [33] => haval192,3 [34] => haval224,3 [35] => haval256,3 [36] => haval128,4 [37] => haval160,4 [38] => haval192,4 [39] => haval224,4 [40] => haval256,4 [41] => haval128,5 [42] => haval160,5 [43] => haval192,5 [44] => haval224,5 [45] => haval256,5

Cordova MySQL PHP Remote Database functionalities -- SOLVED

After bang bang & kickass searches and tens of pages & trial and error -- here come simplest solution for CORDOVA - MYSQL - PHP REMOTE SERVER DATABASE CONNECTIVITY for you. Hope you dont have to PULL YOUR HAIR & GO BALD (as am already bald & getting married on 12th December 2019)

CORDOVA -- CRUD FORM / RESULT PAGE

<!DOCTYPE html> <html> <head> <!-- <meta http-equiv="Content-Security-Policy" content="default-src 'self' data: gap: https://ssl.gstatic.com 'unsafe-eval'; style-src 'self' 'unsafe-inline'; media-src *; img-src 'self' data: content:;">--> <meta name="format-detection" content="telephone=no"> <meta name="msapplication-tap-highlight" content="no"> <meta name="viewport" content="initial-scale=1, width=device-width"> <link rel="stylesheet" type="text/css" href="css/index.css"> <title>DFW Taxi App</title> <style> body { padding:40px; text-align: center;} </style> </head> <body> <h1>register now</h1> <input type="text" name="cname" id="cname" value="narendra modi" /> <input type="text" name="cphone" id="cphone" value="9848848450" /> <input type="text" name="cemailid" id="cemailid" value="namo@htmail.com" /> <button id="submit_btn">Register Now</button> <script type="text/javascript" src="cordova.js"></script> <script type="text/javascript" src="js/index.js"></script> <script type="text/javascript" src="js/jquery.js"></script> <script type="text/javascript"> $(document).ready(function(){ //updateList(); $("#submit_btn").on("click", function(){ var xname = $("#cname").val(); var xphone = $("#cphone").val(); var xemailid = $("#cemailid").val(); success: function(data){ var obj = JSON.parse(data); //console.log(data); //alert(data); // var scode = data[0]; // var uid = data[1]; // console.log(uid); // alert(uid); //alert(obj.uid); alert('Your A/c Has Been Successfully Created'); //window.localStorage.setItem('xid',obj.uid); window.location.href="index.html"; } }); }); </script> </body> </html>

REMOTE CRUD PHP PAGE

// DONT NOT FORGET -- 1st and 2nd line header("Access-Control-Allow-Origin: *"); header('Access-Control-Allow-Methods: GET, POST, PATCH, PUT,DELETE, OPTIONS'); // DONT NOT FORGET session_start(); /// NO WORRIES safe in 3rd line date_default_timezone_set("America/Mexico_City"); //data base connection $srv = 'localhost'; $usr = 'SSDFD787SD7FS'; $pwd = 'dKJFAUDIS'; $db = 'usavedmyarse'; $con = mysqli_connect($srv,$usr,$pwd,$db); if (!$con) { die('Could not connect: ' . mysqli_connect_error()); } // FUCK MAJIC --- PURE programing // manipulate the way you want // dont just give a shit -- hit it $booknow = $_POST; mysql_insert_array('usermst', $booknow,$con); // am using function to save row in mysqli table echo json_encode($booknow);

I would like to thank Code Sundar and Yogesh Sharma --- thank you nerds.

HOPE YOU WILL ENJOY IT!!! PEACE-V

PHP MySQL - Generate Daily Sales Reports

Sorry, I couldn't copy this wonderful --- PHP MySQL Script. Take a Look --- Generate Sales Report - MySQL PHP

PHP : Array to MySQL Table

I knew there has to be some way to simplify whole big process of INSERTing FORM DATA into SQL Table in less time.

Here is How?

1. Collect all form data into an array

    $flds = $_POST;

2. Check Every form data has been collected in ARRAY

print_r($flds);

If things are fine...
3. There is one more things that is to remove "submit" button field

Count Total # of Arrays
$carray = count($flds);

4. Remove "Submit" Array:

array_splice($flds,$carray);

5. If you want to add few fields with default values -- the put them in form HIDDEN FIELDS or Try this to add MANUALLY:

$flds["new_field"] = "new_field_value";

5. Write a Function to extract values from array and insert into MySQL Table

 function mysql_insert_array($table, $data) { 
            foreach ($data as $field=>$value) { 
            $fields[] = '`' . $field . '`';  
            $values[] = "'" . mysql_real_escape_string($value) . "'"; } 
         
            $field_list = join(',', $fields); 
            $value_list = join(', ', $values); 
            
            $query = mysql_query("INSERT INTO `" . $table . "` (" . $field_list . ") VALUES (" . $value_list . ")") or die('Error in Inserting from ARRAY : ' . mysql_error()); 
   return $query; 
   }

6. FINAL NAIL to Good Old Frame for "Wall of Fame" :

     mysql_insert_array("tablename",$flds);

Note: This function can be used without hesitation anywhere, where there is no 2nd table or cross table data storage. Copy code BINDAAS-ly...

Wow!!!! That's Called Music...

It is shameful, even after 8 years into PHP. Never used it. Although the concept was there at cornor of my mind for very longtime (about 5 years) --> That $_POST is an ARRAY ITSELF.

Finally, I worked my way out today for my client Yellow Cab Airport Taxi

Hope you like it...

Creating a Store Locator with PHP, MySQL & Google Maps

Creating a Store Locator with PHP, MySQL & Google Maps

This tutorial is intended for developers who are familiar with PHP/MySQL, and want to learn how to use Google Maps with a MySQL database to create a store locator-type app. After completing this tutorial, you will have a database of locations and a webpage that lets a user enter their address and see markers on a map for the locations nearest to them, within a chosen distance restriction.

Introduction to MySQL Triggers

Introduction to MySQL Triggers

a trigger is a rule that you put on a table which basically says, whenever you DELETE, UPDATE or INSERT something in this table, also do something else. For instance, we might want to log a change, but instead of writing two separate queries, one for the change, and one for the log, we can instead write a trigger that says, “Whenever this row is updated, create a new row in a different table to tell me that the update was made”. It adds a little overhead to the initial query, but since there are not two packets traveling to your database to do two separate things, there is an overall performance gain (in theory anyway).

Using MySQL, Joins

Using MySQL, Joins

using the SQL JOIN Syntax. Most databases have multiple tables to avoid repeating data. i.e. why enter the details of a customer over and over again.

Google Map API -- using MySQL & PHP

Google Map API -- using MySQL & PHP

The example in the article is based on census data. The search screen provides a form to enter a zip code. The results page shows population density for the zip code selected and adjacent zip codes with color-coded "push pins." The map can be moved with a left-click, drag mouse action, and the scale can be changed with the zoom control in the upper left corner of the map.