MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.

Share tech news, updates, or what's on your mind.

Sign up to Post

Hi,
I'm using a form building script, using Ajax Autocomplete, the include file looks like this
<?php
$array = ['Mildred', 'Louise', 'Frances'];


Search 20 results
$max_results = 20;
$found = 0;
$results = [];
for ($i=0; $i < count($array); $i++) {
    if (preg_match('`' . addslashes($_GET['term']) . '`i', $array[$i])) {
        array_push($results, $array[$i]);
        $found += 1;
        if ($found == $max_results) {
            $i = count($array); // end loop if we reached max_results
        }
    }
}

if (count($results) > 0) {
    $out = '[';
    for ($i=0; $i < $found; $i++) { // returns only firs 20 results
        $out .= '"' . $results[$i] . '"';
        if ($i + 1 < $found) {
            $out .= ',';
        }
    }
    $out .= ']';
    echo $out;
}

Open in new window


Problem is I want the array data from a MySQL query, I have tried using an include to an external page that returns the results but fails:

<?php
//Step1
 $db = mysqli_connect('******','****','*****','*********s')
 or die('Error connecting to MySQL server.');
?>


<?php

$query = "SELECT * FROM templates_client_search";
mysqli_query($db, $query) or die('Error querying database.');

$result = mysqli_query($db, $query);
$row = mysqli_fetch_array($result);

while ($row = mysqli_fetch_array($result)) {
 echo "'" . $row['ContName'] . " - " . $row['ContAddress'] . "', ";
}
?>

Open in new window



Any ideas or pointers please?
0
I have in table column named user_date, and I have jquery on frontend to get user status ADULT or YOUNG. When user pick the date, script automaticaly write ADULT or YOUNG, depending on his picked date and current date, = >18 or <18. Now I would, to get number of young users which for one month will be ADULT, and get that number like some alert.
0
Why I'm always getting warning: Natalie_query() expects parameter 1 to be mysqli, null given in c:\xampp\htdocs\view\dokumen.php on line 57
0
I need help with my ASP.NET MVC application hosted on iis. The application loses connection to MYSQL database after sometime and requires me to restart mysql service then it continues to connect.
0
I have one table that contains 2 million rows. at every second there are 6000-row inserting and every second latest row fetching by every group means 6000 new row fetch. so when I try to fetch a particular range of data then the table will be a lock . its show me lock wait timeout. so, what can I do for that? please help me.
0
For my site, I am having trouble make the user redirect to the proper page using header("location: ... ");. Instead, on the log in, it redirects to the form's action: login.php. How do I make it so that the user is redirected to the correct page and not the php file that the login form uses?

Below is the HTML code (the form):
<h2>Log In</h2>
      <form method="POST" action="login.php">
            <input type="number" placeholder="student id" required name="studentid"><br>
            <input type="password" placeholder="password" required name="password"><br>
            <button type="submit" name="submit">Go</button>
</form>

Below is the relevant sections of the PHP:
$uid = mysqli_real_escape_string($conn, $_POST['studentid']);
$pwd = mysqli_real_escape_string($conn, $_POST['password']);

$sql = "SELECT * FROM users WHERE studentid='$uid'";
$result = mysqli_query($conn, $sql);
$resultCheck = mysqli_num_rows($result);

//username check
if ($resultCheck < 1) {
      //redirect and exit
      } else {
            if ($row = mysqli_fetch_assoc($result)) {
                  //De-hashing the password
                  $hashedPwdCheck = password_verify($pwd,  $row['password']);
                        if ($hashedPwdCheck == false) {
                              //redirect and exit
                        } elseif ($hashedPwdCheck == true) {
                              //Log in the user here and get session variables
                              header("location: index.php?login=success");
                              exit();
                        }
                  }
            }
1
I'm working in MySQL after update trigger. when row is updated i need to make a query of changed values so that if that query is run table goes in previous state, for this i need to process each field of the table under some conditions. for this i was thinking to make a procedure to build query and pass that procedure all field's values and names . my tables contains upto 5o columns. therefore i want to know if it is possible to get these values in array form
0
$check_email="select * form users where user email='$email'";
            $run_email = mysqli_query($con,$check_email);
      
            $check = mysqli_num_rows($run_email);
             
               if($check==1){
      
             echo "<script>alert('Email phone already exit,please try another!')</script>";
             exit();
0
I have a MySQL table with more than a 3 billion rows hosted on Google Cloud SQL.

I wish to understand how the total size on disk can be explained from the column data-types, number of rows and the indexes.

I was hoping that it would be something like

size_of_table_in_bytes = num_rows * [ Sum over i {bytes_for_datatype_of_column(i))}
                                    + Sum over j  {Bytes_for_index(j)} ]
But I end up with incorrect disk-size than how much my database size shows.

Using bytes per datatype on

https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html

and additional bytes in InnoDB header and indexes from

https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html#innodb-compact-row-format-characteristics

Here is my understanding of bytes occupied by header, each column and each index

TABLE `depth` (
Bytes| Column/Header/Index
    2| variable length header Ceil(num columns/8) = Ceil (10/8)
    5| Fixed Length Header
    3|`date` date DEFAULT NULL,
    7|`receive_time` datetime(3) DEFAULT NULL,
    8|`instrument_token` bigint(20) unsigned DEFAULT NULL,
    1|`level `tinyint(3) unsigned DEFAULT NULL,
    2|`bid_count` smallint(5) unsigned DEFAULT NULL,
    8|`bid_size` bigint(20) unsigned DEFAULT NULL,
    4|`bid_price` float DEFAULT NULL,
    4|`ask_price` float DEFAULT NULL,
    8|`ask_size` bigint(20) unsigned DEFAULT NULL,
    2|`ask_count` smallint(5) unsigned DEFAULT NULL,
    6|KEY `date_time_sym …
0
$sql="
      SELECT
s.survey_id AS 'Inspection ID',
s.license_number As 'License Number',
s.name_pos AS 'Name of POS',
s.address_pos AS 'Address of POS',
s.updated_at As 'Inspection Date',
s.updated_at As 'Inspection Date',
t.name As 'Town Name',
c.name As 'Districts Name',
s.gps_location As 'Location',
sum(a.option_value) As 'Final Scoring',
a.option_value As 'Remarks'
FROM  ".DB_PREFIX . "survey s
INNER JOIN ".DB_PREFIX."town t
on t.town_id=s.town_id
INNER JOIN ".DB_PREFIX."city c
on c.city_id=s.city_id
INNER JOIN ".DB_PREFIX."answer a
on a.survey_id=s.survey_id WHERE a.question_id=371";

i want sum of option_value WHERE option_id!=0  in join query and  a.option_value As 'Remarks' WHERE a.question_id=371";

from same table
0
i have logged the changes recorded in table "A" using triggers in a table log_tbl with the type UPDATE,DELETE,INSERT . how can i transfer these changes to another table (having same structure as changed table "A") on different server with the help of mysql scheduler OR other technique automatically without REPLICATION.
0
This login form does not open the intended page.  In only opens the error message in the retry.php page.

 Here is part of the code where the problem might be found.  Unfortunately, I do not see it.

<?php
// *** Validate request to login to this site.
session_start();

$loginFormAction = $_SERVER['PHP_SELF'];
if (isset($accesscheck)) {
  $_SESSION['PrevUrl'] = $accesscheck;
//  session_register('PrevUrl');
}

if (isset($_POST['user'])) {
  $loginUsername=$_POST['id'];
  $password=md5($_POST['pw']);
  $MM_fldUserAuthorization = "access";
  $MM_redirectLoginSuccess = "index.php";
  $MM_redirectLoginFailed = "tryagain.php";
  $MM_redirecttoReferrer = false;
  mysql_select_db($database_ibtib_dbc, $ibtib_dbc);
        
  $LoginRS__query=sprintf("SELECT user, pw, access FROM users WHERE user='%s' AND pw='%s'",
  get_magic_quotes_gpc() ? $loginUsername : addslashes($loginUsername), get_magic_quotes_gpc() ? $password : addslashes($password));
   
  $LoginRS = mysql_query($LoginRS__query, $ibtib_dbc) or die(mysql_error());
  $loginFoundUser = mysql_num_rows($LoginRS);
  if ($loginFoundUser) {
   
    $loginStrGroup  = mysql_result($LoginRS,0,'access');
   
    //declare two session variables and assign them
    $_SESSION['MM_Username'] = $loginUsername;
    $_SESSION['MM_UserGroup'] = $loginStrGroup;           

    //register the session variables
//    session_register("MM_Username");
//    session_register("MM_UserGroup");

    if …
0
<?php
//Step1
 $db = mysqli_connect('localhost','username','','db_name')
 or die('Error connecting to MySQL server.');
?>

<html>
 <head>
 </head>
 <body>
 <h1>PHP connect to MySQL</h1>
 
<?php
//Step2
$query = "SELECT * FROM imabangalore";
mysqli_query($db, $query) or die('Error querying database.');
$result = mysqli_query($db, $query);
$row = mysqli_fetch_array($result);

while ($row = mysqli_fetch_array($result)) {
 echo $row['Name'] . ' ' . $row['IMA Memb No'] . ' ' . $row['Spouse Name'] . ' ' . '<br />';
}
mysqli_close($db);
?>

</body>

</html>

Open in new window


Results shows all the data, but I want only the searched Name and their details
0
Hi Experts;
I've had success at migrating a complex stored procedure from MS SQL to MYSQL but am stumped at a line using PARTITION OVER.

I can't figure out how to convert the following code:
MS SQL code:
                  SELECT *,
                        t.GId,
                        t.PId,
                        t.GABNum,
                        t.PTABNum,
                        t.paeid,
                        RowNumber = ROW_NUMBER()
                        OVER(PARTITION BY t.GId, t.PId, t.GABNum ORDER BY t.GABNum, t.PTABNum)

                  FROM
                        mydb.dbo.myTable t

The above is a derived table within a larger query and eventually I want MIN(t.paeid) and MAX(t.paeid)

I've commented out the partition line and am able to get matching results except for the MAX(t.paeid) which makes sense.

I've been trying several methods found on the internet, and so far I've gotten this far (to get the MIN value). However it returns 0 rows so I'm doing it wrong.
MYSQL code:

use mydb;
set @r = 0;
select
@r AS Firstpaeid
      FROM (SELECT *
                  FROM (SELECT @_gid = NULL, @_pid = NULL, @_gabnum = NULL) vars,
                        mydb.myTable
                  ORDER BY GId, PId, GABNum)
        WHERE (CASE WHEN @_gid <> GId AND @_pid <> PId AND GABNum <> @_gabnum THEN
                  @r := paeid END)
                            AND (@_gid = GId)
                            AND (@_pid = PId)
                            AND (@_gabnum := GABNum)

Perhaps I should be using a different method entirely? I've read about CONCAT(GROUP BY...) and am trying that method now.
I also haven't worked with Partitions in SQL (or MYSQL) so …
0
I have a pagination that load more when I click on the load more button, which I got from the below URL http://makitweb.com/load-more-results-with-jqueryajax-and-php/

Whenever I add RAND() it always duplicates the value.

index.php

<?php

$rowperpage = 10;

$allcount_query = "SELECT count(*) as allcount FROM users order by id";
$allcount_result = mysql_query($allcount_query);
$allcount_fetch = mysql_fetch_array($allcount_result);
 $allcount = $allcount_fetch['allcount'];

$query = "select * from users order by RAND(" . date("Ymd") . ") asc limit 0,$rowperpage ";
$result = mysql_query($query);
while($row = mysql_fetch_array($result)){
?>

<div><h1><?php echo $row['id']; ?></div>

<?php
}
?>

<h1 class="load-more">Load More</h1>
<input type="hidden" id="row" value="0">
<input type="hidden" id="all" value="<?php echo $allcount; ?>">

Open in new window


getData.php

<?php
$row = $_POST['row'];
$rowperpage = 10;


$query = 'SELECT * FROM users ORDER BY id limit '.$row.','.$rowperpage;
$result = mysql_query($query);

$html = '';
while($row = mysql_fetch_array($result)){

$html .= '<div><h1>'.$id.'</h1></div>';

}
?>

Open in new window

0
I've read a few tutorials online for sending a blank image in an html email in order to log when the email is opened, but none of them seem to work in my system.  I don't need anymore information than the date/time an email was opened to log in my MySQL database.  Any help would be appreciated!
0
HI All, trying to figure out how to use data from two tables in Domo.

This is what I'm trying and it is not working.

SELECT id, jobsite, status FROM application UNION SELECT client, id, nickname FROM jobsites

Open in new window


It's only showing the first part, however I do not get any errors.

Any help is greatly appreciated.
0
I have the following query on table fila

SELECT * 
FROM fila f
JOIN meta m
ON m.link = f.idlinks
WHERE m.meta2 < m.meta

Open in new window

It works, but I need something more ...
I need the query to get into the FILA table values that are no longer in the activity table, for example:
If the activity table already exists in the URL column and in the ACAO column, that data is disregarded and I get the next item in the row table

Example:
Table Fila
Table fila
Table Atividade

atividade2.pngAs already exists in the activity table at URL 976 and USER 92, that record has to be disregarded and the query move to the next record in the FILA table
0
We are looking at deploying SuiteCRM Company wide.  We have a development environment which we are testing all the required features and modules that we will required, and at the same time demoing to the management teams what the system will eventually look like.

We have a problem in that at current we have around 6 different websites/microsites that are hosted and designed by 6 different companies (all sadly managed by our marketing department, hence 6 companies!)

At current the contact form comes in to an email address (webenquires@) and then gets routed via our ancient mailserver.

We will be migrating to office 365 the same time as we roll out the new CRM.

I am looking for a solution to automatically create a lead from an inbound email in SuiteCRM.
Yes I am aware of doing it via campaigns and forms, however trying to get all 6 companies to make the correct changes at go live the same time will fail.  

Also we need to demo this system for a few weeks to various management teams, and would like to implement the form changes 1 site and company at a time allowing for problems.

What I need is a temporary solution to do this via:

Either MySQL or inner Join
PHP coding
Or some kind of workflow

The correct inbound email address (webenquiries) is already configured and working in SuiteCRM.

I tried the following inner join from the example above which is based on Sugar CE6.5 failed.  I modified it to work with SuiteCRM and it now generates a Lead, …
0
I am using jxDatePicker to accept date data in my java project but MySql is not recognizing it as Date despite setting the Table Data as DATE and formatting the jxDatePicker value to "yyyy-mm-dd" format in the code.
I will appreciate your help to fix this problem.
0
Dim ds As DataSet = dbc.data("CALL sv_Int_months_final1 ();")
0
How do I move MySql database to new server?
0
I have a username and password for the real estates agent theysaid I have to call the web service and get a response but
How can I integrate to my theme in wordpress if u want to help me will be good or I can hire you too but I like to learn anny I wait for your answer guys thank you
0
I am connecting to MySQL server using mysql command line utility.  Server version: 10.2.6-MariaDB-log.  

I am loosing connection after about 10 min executing my stored procedure.  Stored procedure created a cursor, iterates it, and does something.  It is not stuck at the time connection is lost.  What setting is causing this?  How can this timeout be increased?

Please note, I am not running my stored procedure in MySQL Workbench.
0
After executing two seperate 'WITH' statements, I can show them seperately and they will show the correct data.
However, there seems to be no way to combine the two results in one table to the user.

UNION gives you a timeout.
You cant seem to create a temporary table to append to, because the recursive WITH seems to not allow this.

WITH recursive temp as (
   SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val
   FROM templatevalues
   WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point
   UNION ALL
   SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val
   FROM templatevalues c
   JOIN temp p ON p.parent_template = c.current_template AND p.parent_iteration = c.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion
),
temp2 as (
   SELECT current_template, current_iteration, parent_template, parent_iteration, tkey, val
   FROM templatevalues
   WHERE tkey='linnumber' AND val='1' AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the starting point
   UNION ALL
   SELECT c.current_template, c.current_iteration, c.parent_template, c.parent_iteration, c.tkey, c.val
   FROM templatevalues c
   JOIN temp2 p ON c.parent_template = p.current_template AND c.parent_iteration = p.current_iteration AND guid='ffb7e0cd-5dff-4b83-be88-ebf66d5557be' -- this is the recursion
)
0

MySQL Server

47K

Solutions

23K

Contributors

MySQL is an open source, relational database management system that runs as a server providing multi-user access to a number of databases. Acquired by Oracle in 2009, it is frequently used in combination with PHP installations, powering most of the WordPress installations.