[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

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 experiencing difficulty importing fields in a CSV File that include german characters or umlauts e.g. Ü

The CSV contains a number of columns with one in particular named "itemTitle" which contains the word SCHÜTZEND which changes to SCHÃœTZEND, after the import has finished. I only noticed that SCHÜTZEND had changed to SCHÃœTZEND when I tried to perform an update query based on the field "itemTitle" as part of a data clean up.

What I mean by that is the HTML page correctly renders the word as SCHÜTZEND, however, when I examined the table via terminal I can see the actual value stored is "SCHÃœTZEND".

The field collation for itemTitle is the default: latin1_swedish_ci and the type is varChar(255). Just so that you know I've changed the collation to UTF-8 and many other varieties to no avail.

I'm using a LAMP setup and a snapshot of the import routine is shown below: -

if(isset($_POST['submit']))

{
         
$file = $_FILES['file']['tmp_name'];
        
$handle = fopen($file,"r");
fgets($handle);
        
while(($fileop = fgetcsv($handle,10000,",")) !== false)
        
{
$UserId = $mysqli->real_escape_string($fileop[1]);
$itemTitle = $mysqli->real_escape_string($fileop[12]);

$UserId = mb_convert_encoding($UserId, "UTF-8", "Windows-1252");
$itemTitle = mb_convert_encoding($ItemTitle, "UTF-8", "Windows-1252");
           				
$query = "INSERT into holding_csv
                                        
(userID ,itemTitle)  

Open in new window

0
I need to update my mysql table for tax codes in woo commerce 3.4.  What query would I used to put in tax code PH400930 for all products in the essentials_oil category?

Thanks,
0
Hi Expert(s),

Is there a way to write Data (eventually with SQL)  from the AS400-DB2 to a MySQL-DB or Oracle?
0
hi,

any clear to understand and follow installation guide to quickly setup MySQL InnoDB cluster? 3 x DB  node and 1 x nodes for MySQL router and MySQL Shell ?

we are told to use InnoDB cluster but not NDB cluster.


test guide are also good.
0
I need to display the attribute value on the checkout page for each product being purchased.  I am  using Woo Commerce 3.4.  Please give me the syntax to access the value.

Thanks,
0
Hi,
suppose i have a mysql table  with rows (originalUrl : varchar(500) , shortUrl : varchar(10))
The queries that will be executed on this table will mostly be
1. select * from table where shortUrl = X
2. insert into table (originalUrl, shortUrl)

So there should be an index on shortUrl to speed this up.

I have the following question -
1. What exactly the index table will store ?
My understanding is index table will store items like - (shortUrl, pointerToDisk) // where pointerToDisk will locate exactly the place in disk where the row is stored.

2. Where is index table stored ?
Is it always stored in Disk or memory ?

3. What is the size of index table exceeds that of RAM ?
In this case the full index table will never be in RAM and so how will queries like select * from table where shortUrl = x execute
Will a part of index table be pulled out everytime to check the location ?

4. In case where this table is very huge say 3 TB. How big will index table be...

5. If index table is larger than size of RAM and since then the queries will take a lot of time. Is there a better alternative ?? Like using noSQL database. or storing data in two machines splitting them rather than on one machine ?

Thanks
0
Hello,

I have the following MySql select which shows those records where the customer uploaded more than one picture for the same product:

 select ao_autoinc1 ,count(*) db  from _autoinc_join
 left join product on(product_id=ao_autoinc1)
   left join product_pictures on(picture_product_id=ao_autoinc2)
  group by ao_autoinc1

having count(*)>1

The product table and the product_pictures are connected via _autoinc_join table (because many products can share the same picture).

I need to delete the pictures in such a way that only on picture should remain per product.
e.g. no matter if its 67 pieces or 2 pieces, it should remain 1 picture.

How can I do that?

Thank you!
0
Hi Experts

Could you point how to populate Magento 1.9.x with data - insert table contents?

The database is correctly created but the tables are empty, making it dificult to start.

The data template that was given only reacreates the table structures without any data inside.

Thanks in advance.
0
Hi,
I am planning to build a service like shortening url for learning purpose..I was working on it theoritically.
And realized that i would be needing to store approximately 32 GB of data in the long run...
It will be mostly key value pair data  
eg -
1) www.bity.com/abcdefg  |  www.klasjlkajsf.comalskdfjlaksdjflkasjdfl
2) www.bity.com/abcdefg  | www.laksdjflkasjdflkasd.aldskjflkasdjflkasdf//alsdkfjalsdfj

So i was wondering which database will be better.
Dont know how Mysql will behave when total data reaches 32 GB ?
How long will it take to query it..
Is there some theoritical way to find it out. I think putting an index on the first column should be really fast kind of binary search ?
What are the alternatives  or best way ?
I read about redis that seems to be an in memory database so should be really fast. But it probably cannot store more data than the amount of RAM.
is redis feasible when the amount of data is so big like 32 GB ?
Do we need 32 GB of RAM or there are alternatives ??


Thanks
0
Hi there.

I have a big problem. I'm trying to import data from MySQL to MS SQL Server.  SQL Server Migration Assistant for MySQL won't show tables for my SQL Server... odd... works fine in SSMS. I also tried importing dumps etc... and here I encounter a problem like this:

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

In MySQL I used the date format "date". In SQL Server I use "datetime". Is that a problem? In all tables I also have an auto generated date for events: that is "timestamp" in MySQL and also "datetime" in SQL Server.
Any tricks or work around for this issue?

Best regards

Ulrich
0
Hello
I have a vps server with mysql apache andphp installed, and working codeigniter php script.
I have the SSh password. but I lost my phpmyadmin uername password.
What can I do?
0
I need to add into the code below an IF statement (in the commented area), checking that the order number and a few other things are greater than 0... if they aren't, I need to return a message to the browser saying "no good" or whatever. If they are, then the code keeps going. The code below is tested and it worked, but now I just need that break in the middle that checks for data validation before inserting into the db and sending the email.
    public function indexAction()
    {

       $request_result = array('success' => false);
       if($this->get('security.context')->isGranted('ROLE_USER')) {
          $em = $this->getDoctrine()->getEntityManager();
          $dbAzure = new DbAzure();
          $user = $this->container->get('security.context')->getToken()->getUser();
          $request = $this->getRequest();
          
          $om_customer_id = $user->getCustomer()->getOmCustomerId();
          $order_number = $request->get('order_number');
          $qm_sku = intval($request->get('qm_sku'));
          $options = $request->get('options');
          $qty = intval($request->get('qty'));
          $email = $request->get('email');
          
          
          // Verify data is correct.. order_number > 0....om_customer_id > 0.. qm_sku > 0... qty > 0
          // if not value... don't write the info and return an error

          
          $request_result['success'] = $dbAzure->submitReturnRequest($order_number, $qm_sku, $options, $qty);
          
          

Open in new window

0
Hi,

There is something wrong with the syntax on the following line of code I created.
$sql = "SELECT * FROM teachers_table WHERE id = '$row.['teacherid']'"; 

Open in new window


I have three tables. A teachers_table, a students_table and a favourites.  The favourites table consists of a unique id and a teachers id and a students id. The teachers id in the favourites table matches the teachers id in the teachers table and the students id in the favourites table matches the students id in the the students table.

So I started by using INNER JOIN to get the teacher ids which are contained in both teachers_table and favourites for the student id (of the student logged in).

$sql = "Select teacherid FROM favourites INNER JOIN teachers_table ON favourites.teacherid = teachers_table.id WHERE studentid = '$ids'";
$result = mysqli_query($db_connection, $sql); 		

while ($row=mysqli_fetch_assoc($result)) {
//echo $row['teacherid']."<br>";

Open in new window


This appeared to work, as the echo statement above correctly displayed the teacher ids in the favourites table of the student logged in.

My next step was to display more than just the teacher ids. I wanted to display all the information in the teachers_table associated with those ids:

            
            
$sql = "SELECT * FROM teachers_table WHERE id = '$row.['teacherid']'"; 
		$result_favs = mysqli_query($db_connection, $sql); 

Open in new window


However, the following error check showed that that syntax isn't correct.
            
            if($result_favs === false){
            echo"<b>Query failed!</b><br/>\n<b>Query:</b>{$result_favs_sql}<br />\n<b>Error:</b>".mysqli_error($db_connection);
            exit();
            }


It returned an error relating to the right syntax to use near 'teacherid']'' at line 1
0
How do I query the Woo Commerce product title looking for this string "- [Order Quantity" and change it to "[Order Quantity" which is merely removing the dash and space?
0
dataTable boostrap Table How to hide duplicate values on cell?..
Form this

and I want to display my table like this

How can I achieve this experts?...
0
In Below Scenerio, Will Master-slave replication should work?

I have local and live Server. Everyday i have to insert data from local machine which needs to be synced to live Server(1 time per day) and changed data needs to be sync back to local.
0
I have this Windows Server 2016 with MySQL 5.7 installed, I am trying to connect a Windows XP workstation to the SQL Database and when I configure the ODBC connector and click on Test, I get the message "Host is not allowed to connect to this MySQL server"
I already opened the Inbound port 3306 in the firewall
What could be the problem?
Thanks
0
I have received a copy of what should be a mysql database in the format *.sql. I need to try and restore it to see if it contains some data we need to move into an archvie access database. Are there any free tools that can be used to browse the mysql data that run on windows? I am not sure if the sql format is what mysql uses as an extention for its data, e.g. similar to mdf for MSSQL
0
hi,

I am not encountering MariaDB cluster and this is what I got.

enable mariadb command ok, but when start it, it shows:

start service
and the mariadb.service start error in detail:

detail
and this is the server.cnf setting for my mariaDB:

server.cnf
it is funny that last friday everything is ok, but this monday is not ok.

any idea?
0
Hi,

Now our MySQL cluster test platform do not have internet connection and we need to setup local repository for it.

I check out a lot of googled page and it seems no one exactly match what I am going to install, let see what I go from latest MySQL cluster 7.6.6 binary:

rpm files list.
so for management node, which rpm I should install and what is the command for it? just yum --noplugin localinstall <rpm name> ?

For data node, which rpm I should install?

For SQL  node, which rpm I should install ?
0
</form><br />
<form action="searchDate.php" method="post">
<label>3. Search by Site Visit Date :</label><input type="text" name="searchdate" id="date"></a>
<input type="submit" value=" Submit " name="submit"/><br />
</form><br />

Open in new window


I am trying to insert a date into the mysql databse:
the format is 23/02/2018  dd, mm, yyyy
Can I have help on the format of the mysql database field and how to submit this to the database, then how to retrieve it
0
I have the following code which is working well:
[code]
<?php
$sql = "SELECT sites_id, sites_name, sites_email, sites_company FROM inventassites";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id:" . $space.$row["sites_id"]. " - Name: " . $row["sites_name"]. " email address: " . $row["sites_email"]. " Company: " . $row["sites_company"]. "<br>". "<br>";
    }
} else {
    echo "0 results";
}
$conn->close();
?>

Open in new window

What I am trying to do is to format the following output ( while($row = $result->fetch_assoc()) {
        echo "id:" . $space.$row["sites_id"]. " - Name: " . $row["sites_name"]. " email address: " . $row["sites_email"]. " Company: " . $row["sites_company"]. "<br>". "<br>";
    }) so that the $row values are formatted such that they appear in input boxes or text areas.
I would like to place input boxes or text areas on the page in the <body> tag and the have these areas populated by the script so that I will get a set of rows on the page each with a value in it from the query
[/code]
0
Goal: Toggle sorting a column of dates in descendingascending order including the entire row in a table. The in-page PHP currently sorts some columns. We need to add sorting for all columns in the table. I tried applying the same input tag format to two columns that were not set to sort. Specifically, the Commissions and Referrals columns will not sort when applying the same input tag format as other columns that sort correctly. Both buttons throw the following error: Unknown column 'Agent_Commission' in 'order clause'
agent_page8-test.php
0
Before I write something from scratch, I am looking for Code or SW or 3rd party App that will provide Search functionality to a website ...
HOWEVER I want to search a Database (MySQL/PHP), not search the existing web pages.
You know, like "Click here to search our database of 100000 new graduates" and receive the results back on their screen.  I suppose the DB Query code is straight forward ... but I was looking for help in the "Choose from these filters" part.
Any ideas?
- B
0
I want to get 4 random database records but I have read that it is bad to use the following if you have a lot of records:

SELECT somefield FROM mytable ORDER BY RAND() LIMIT 4

Open in new window


It seems like a better way would be to count the number of records and then select a random number less than the count and use an offset in the limit clause.

I tried this and it sort of works but sometimes only 1 or 2 records will display instead of 4 and sometimes nothing displays at all. I always want 4 records to show.

Here is my first query that counts:

$this->db->query("SELECT COUNT(`prod_id`) AS totalProds FROM `bb_products`");
$totalRows = $this->db->single();
$rand = mt_rand(0,$totalRows->totalProds -1);

Open in new window


Then I have an extremely long query but it ends in:

("SELECT FROM.....
LIMIT :limit, 4");

$this->db->bind(":limit", $rand);
$results = $this->db->resultSet();
return $results;

Open in new window

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.