Skip to main content

Posts

Showing posts with the label sql

MySQL INTO OUTFILE overide existing file?

I've written a big sql script that creates a CSV file. I want to call a cronjob every night to create a fresh CSV file and have it available on the website. Say for example I'm store my file in '/home/sites/example.com/www/files/backup.csv' and my SQL is SELECT * INTO OUTFILE '/home/sites/example.com/www/files/backup.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM ( .... MySQL gives me an error when the file already exists File '/home/sites/example.com/www/files/backup.csv' already exists Is there a way to make MySQL overwrite the file? I could have PHP detect if the file exists and delete it before creating it again but it would be more succinct if I can do it directly in MySQL. Source: Tips4all

Are there any differences between SQL Server and MySQL when it comes to preventing SQL injection?

I am used to developing in PHP/MySQL and have no experience developing with SQL Server. I've skimmed over the PHP MSSQL documentation and it looks similar to MySQLi in some of the methods I read about. For example, with MySQL I utilize the function mysql_real_excape_string() . Is there a similar function with PHP/SQL Server? What steps do I need to take in order to protect against SQL injection with SQL Server? What are the differences between SQL Server and MySQL pertaining to SQL injection prevention? also - is this post accurate? is the escape string character for SQL Server a single quote? Source: Tips4all

How to auto insert Current DATE in SQL with Java / Hibernate

I need to add automatically the current date into my Database when I create a new OperantionBank. I'm using Hibernate. Thanks import java.io.Serializable; import java.sql.Date; import javax.persistence.*; import org.hibernate.annotations.Generated; import org.hibernate.annotations.GenerationTime; @Entity public class OperationBank implements Serializable { private static final long serialVersionUID = 1L; @Id @GeneratedValue(strategy = GenerationType.AUTO) private Long id; private String wordring; private Double amount; @Generated(GenerationTime.ALWAYS) @Temporal(javax.persistence.TemporalType.DATE) private Date dateoperation = new java.sql.Date(new java.util.Date().getTime()); @OneToOne private Account account;

Subquery returns more than 1 row

All I have two table 1st table is : wp_frm_item_metas 2nd table is : wp_frm_items Now i want to meta_value based on wp_frm_items table fields value. I fired this sql. it returns me Subquery returns more than 1 row SELECT meta_value FROM wp_frm_item_metas WHERE (item_id=( SELECT id FROM wp_frm_items WHERE form_id ='9' && user_id='1') && field_id=128) I tried this solution SELECT meta_value FROM wp_frm_item_metas WHERE (item_id=( SELECT count(*) as c,id FROM wp_frm_items WHERE form_id ='9' && user_id='1') && field_id=128 && c > 1) ORDER BY c DESC It returns this error Operand should contain 1 column(s) My code is foreach($fp_id_c as $kid=>$id) { if (!$id or ($logged_in && !$user_ID)) return; $id = (int)$id; //echo $logged_in.'-'.(int)$user_ID; if ($logged_in){

How to manipulate a rowset data from sql to php

I have asked a question about how to retrieve data from different tables in sql How to get data from 4 tables in 1 sql query? When I run the query for my sample data I get 3 rows, anyway being many-to-many relationship between courses and categories I will always get more rows for same course. My question is how do I handle this data in PHP? I get an array and what I want would be something like: Array ( [0] => stdClass Object ( [name] => course name [tutor] => tutor name [categories] => Array ( categories here ) ); Should I just fetch the data from the categories in a foreach loop after I fetch a course? That would mean bad performance for large amount of data.

Is it preferred to assign POST variable to an actual variable?

I've just completed my registration form for my website and for the action page where all the SQL takes place I've just skipped assigning the POST variable to actual ones, like this... $username = $_POST['username']; Instead I've just been using the POST variables throughout the PHP page. Are there any risks or errors that can be met whilst practicing? Also, excuse me if I am using incorrect terminology...

Count data from table SQL

my query: "SELECT id FROM user_follow WHERE user_id IN(3,6) AND follow_id NOT IN(3,6) GROUP BY follow_id"; and it show : [users] => Array ( [0] => Array ( [id] => 20 ) [1] => Array ( [id] => 9 ) [2] => Array ( [id] => 19 ) ) I need to count data (in this example query must give back 3 as result) I tried COUNT(id) but result was not good for me. result : [0] => Array ( [count(id)] => 1 ) I tried SELECT count(1) result : [0] => Array ( [count(1)] => 1 ) [1] => Array ( [count(1)] => 2 ) [2] => Array ( [count(1)] => 1 ) Still not not one number (3). I want someth