How does distinct work with the following table:
id | id2 | time
-------------------
1 | 5555 | 12
2 | 5555 | 12
3 | 5555 | 33
4 | 9999 | 44
5 | 9999 | 44
6 | 5555 | 33
select distinct * from table
dimanche 26 juin 2016
Distinct not working as expected with sql
SQL Server sp_columns does not return result
I was trying to check the data types of each column. I have tried the code below.
use AdventureWorks2014
exec sp_columns Person;
However, the result return something like this.
PS: I am using AdventureWorks sample database.
MySQL pivot data
I have data.
A, STATUS, P
A1, 1, P1
A1, 1, P2
A1, 1, P3
A2, 1, P3
A2, 1, P4
A2, 1, P5
A3, 0, NULL
I want result same
P, A1, A2, A3
P1, 1, 0, 0
P2, 1, 0, 0
P3, 1, 1, 0
P4, 0, 1, 0
P5, 0, 1, 0
How can I do it with mysql query?
Image upload path in MAC style instead of Windows style PHP Ecommerce
I have issues with uploading pictures on my local development environment[enter image description here][1] XAMPP,
[What is shows when i use inspection tool to check the directory instead c//; ecom/resources/upload/name of image i get c/recource/enter image description hereuploadname of image
Function to addproduct
How to select a particular row of query result
I have a following table in my Oracle database:
How can I select the course which is done by most of the students?
I am trying multiple variations in following SQL query but it's not working -
select count(course) as pcourse, course
from studies
group by course
order by pcourse dec;
SQL Error - has more columns than were specified in the column list
I'm trying to retrieve results from the below query but keep getting this error message:
Msg 8158, Level 16, State 1, Line 1
'a' has more columns than were specified in the column list.
My query:
select
*
from
(select
customer_key as customer_id, updated_by as [Help Desk]
from
permission) a (nolock)
where
[Help Desk] is not null
Update table1 column if table2.date > NOW()
How can i update specific column in table1 only when table2.date is >= NOW(). I tried
UPDATE table1 JOIN table2 ON (table1.ownerid = table2.ownerid) SET table.test = 'disabled' WHERE FROM_UNIXTIME(table2.dateto) <= NOW();
But its seems to now work and no error at all
Date difference in days with decimal value, and excluding weekends
I would like to get the exact amount of days between two timestamps (ex. 1.45 days). The thing is BigQuery datediff function rounds the day and only accepts 2 timestamp arguments.
SELECT
datediff(start_time_pac_tz, end_time_pac_tz) as Date_difference
Date_difference
-6
Also I'm looking to exclude weekends. Any help is greatly appreciated.
MySQL Installer 5.7.13 Internal Error
I am new to mysql. I have downloaded mySql installer 5.7.13 of 320.2mb size. While installing i get error Internal Error(error retrieving product version) the installer will now close. screenshot Please can you help me to fix this.
Thanks in advance!
Use update statement in where clause
Is it possible to use an UPDATE statement within the WHERE clause of a SELECT statement?
I would like to execute something like this but it doesn't work:
SELECT * FROM table1 WHERE col1 < (UPDATE table1 SET col2='test' WHERE id=1)
I use mysql_query in PHP.
SQL command to view next command used
Is there a command like F9 which goes to previous command to the next command or a way to view all commands used? Using the retrieve command in zeus to go back to the command that was just used, how would i go to forward after using the F9 when i've gotten to far. Instead of restarting my search.
Check Before Inserting
I need to check a value before inserting. However for some reason I can't figure it out. Here's my code:
set @accID = (select id from table2);
IF @accID IS NOT NULL THEN
INSERT INTO task
(account_id)
VALUES (@accID);
END IF;
What is wrong with the code above as it shows invalid sql syntax error?
grouping a datetime column only by time
I have a column datedate format, with 15 min intervals and another column called datavalues with corresponding data. I want to roll up the data values to 1 hr. I am attaching a screen shot. 
mysql group by and order by id breaks
I have a table with columns
id, q_id, Question, Answer, Q_Date
I have made a query which concatenates multiple rows having same q_id.
Here is the query:
select q_id, Question, Link, Q_Date,
GROUP_CONCAT(Answer SEPARATOR 'n') as Answer
from ask
group by q_id, Question, Link, Q_Date
What I want to do is order by the id column, but when I select id column, it shows all the rows, UnConcatenated.
please help
Php Select Statement works with id as record selector but will not work if I use a different column as a selector
Php Select Statement works with id(with unique values) as record selector but will not work if I use a different column(with unique values) as a selector
THIS WORKS
$Idart = "4";
$sql2 = "SELECT * FROM articles where id in ({$Idart})";
$results2 = $conn->query($sql2);
$row2 = $results2->fetch_assoc();
THIS DOES NOT WORK
$Idart = "5-6142-8906-6641";
$sql2 = "SELECT * FROM articles where IDStamp in ({$Idart})";
$results2 = $conn->query($sql2);
$row2 = $results2->fetch_assoc();
I've tried a variety of different things with MYSQL including deleting "id" column and making "IDStamp" the primary key. Any thoughts appreciated.
SUM two different tables columns in SQL ACCESS
Trying to figure out how to get this JOIN to work properly. Been sitting here for about 30 minutes. Can someone help me out? I am trying to subtract one form the other, to see the difference between invoice quantity and inventory volume.
SELECT Invoice.NameOfItem, SUM(Inventory.Volume - Invoice.Quantity) As TotalNeeded
FROM Invoice
INNER JOIN Inventory
ON Invoice.NameOfItem=Inventory.NameOfItem
GROUP BY Invoice.NameOfItem;
The issue is the output is incorrect.
SELECT NameOfItem, SUM(Quantity) AS TotalNumberNeeded From Invoice GROUP BY NameOfItem
subtracted from
SELECT NameOfItem, SUM(Volume) AS TotalNumberNeeded From Inventory GROUP BY NameOfItem
Is = -112. The output is currently "992"
Output results in a single table in console
I'm running the below code using POSTGRESQL in Jetbrains. I'm trying to output the results in a neat 2 column table (QUARTER, RESULTS) INSIDE the console. When I run the below code, it comes back, but in separate tables, making it annoying to have to consolidate the results from each. Is there a way to get multiple results in the same table so I can copy and paste the results INSIDE the console? Thank you
I'm running
SELECT COUNT(DISTINCT CUSTOMER) as Q216 FROM(
SELECT *
FROM TABLE
WHERE CUSTOMER IN ( SELECT CUSTOMER
FROM temp_08.Unemployment
WHERE TRANSACTION_DATE > '3/31/2016'))
SELECT COUNT(DISTINCT CUSTOMER) as Q116 FROM(
SELECT *
FROM temp_08.COF
WHERE CUSTOMER IN ( SELECT CUSTOMER
FROM temp_08.Unemployment
WHERE TRANSACTION_DATE between '12/31/2015' and '3/31/2016'))
Mysql to use all cores 20 cores I have
I am currently setting up a website that uses a mysql database having more than 4 crore rows of data. I am using one server for files and separate mysql server. Two servers are having 20 core, 64 gb ram. Meanwhile I could see while executing longest mysql queries my mysql server is using two cores maximum hence it is taking more than 38 seconds to execute my longest query. see the result below.
User@Host: dev_data @ localhost []
Query_time: 38.113460 Lock_time: 0.000514 Rows_sent: 10 Rows_examined: 48683733
How can i configure my mysql server so that it uses all the 20 cores to handle the mysql ? How can I achieve this ? Mysql version using 5.6
Atop result below.
samedi 25 juin 2016
Python, Flask -- count page visit and write in database
There is a small hello world Flask with visit statistics. I managed to count visits with redis, but I also have to add support storing date and number of visits in MySQL database. The current code is: I was trying to access previously created database. Currently I have no idea how to store such info in MySQL.
I was trying to access previously created database. Currently I have no idea how to store such info in MySQL.
from flask import Flask
from redis import Redis
app = Flask(__name__)
redis = Redis(host="redis")
mysql = MySQL()
app.config['MYSQL_DATABASE_USER'] = 'user'
app.config['MYSQL_DATABASE_PASSWORD'] = 'passwd'
app.config['MYSQL_DATABASE_DB'] = 'hello'
app.config['MYSQL_DATABASE_HOST'] = 'mysql'
mysql.init_app(app)
@app.route("/")
def hello():
visits = redis.incr('counter')
html = "<h3>Hello, world!</h3>"
"<b>Visits:</b> {visits}"
"<br/>"
return html.format(visits=visits)
if __name__ == "__main__":
app.run(host="0.0.0.0", port=80)
How this can be solved? I will be grateful for any suggestions.
PostgreSQL backdating query
I am trying to write a query that will return counted records from the time they were created. The primary key is a particular house which is unique. Another variable is bidder. The house to bidder relationship is 1:1 but there can be multiple records for each bidder (different houses). Another variable is a count (CASE) of results of previous bids that were won. I want to be able to set the count to return the number of previous bids won at the time each house record was created. Currently, my query logs the overall number of previous bids won regardless of the time the house record was created. Any help would be great! Example:
SELECT h.house_id,
h.bidder_id,
h.created_date,
b.bids_won
FROM house h
LEFT JOIN bid_transactions b
ON h.house_id = b.house_id
LEFT JOIN (
SELECT bidder_id,
COUNT(CASE WHEN created_date IS NOT NULL AND transaction_kind = 'Successful Bid' THEN 1 END) bids_won
FROM bid_transactions
GROUP BY user_id
) b
ON h.bidder_id = b.bidder_id
ORDER BY j.created_date DESC
Properly formatting an execute alter with variable and table name in postgres
I'm trying to execute on an alter table to assign a column to a sequence nextval for an auto-increment, but can't seem to figure out how to do this last part. The sequence is created fine, owners are all asigned, and table_a.id is the owner of table_a_id_seq. I've already created table_a_id_seq.
In a postgres sql function, how do I format this correctly.
I've tried:
EXECUTE format('ALTER TABLE ONLY %s ALTER COLUMN id SET DEFAULT nextval($1::regclass)', new_table_name) USING new_seq_name;
But it says that $1 is not pointing to new_table_seq_name.
I've also tried:
EXECUTE format('ALTER TABLE ONLY %s ALTER COLUMN id SET DEFAULT nextval("%s"::regclass)', new_table_name, new_seq_name);
But it tells me the sequence doesn't exist which makes me wonder if it needs to be behind a transaction separated by this statement.
How can I successfully execute this alter on new_table_name? Thanks for the help!
using groupby and order by with inner join
I am trying to use inner both GROUP BY and ORDER BY command in a same query with INNER JOIN, its is not happening.
i have Employee table:
+------------+------+
| id | fname | lname| |
+------------+------+
| 11 | ABCD | XHME |
| 12 | CDEF | LMOP |
| 13 | MNOP | DDDD |
+---------+---------+
emp_details table
+----+--------+-----------+--------------+--------------+-------+
| id | emp_id | company | joining_date | Leaving_date | salary|
+---------+---------+--------------------+--------------|-------+
| 1 | 11 | Company 1 | 1999-01-03 | 2001-07-08 | 12000 |
| 2 | 11 | Company 2 | 2005-07-09 | 2007-01-31 | 16000 |
| 3 | 11 | Company 3 | 2002-04-07 | 2015-04-28 | 23000 |
| 4 | 12 | Companyxyz| 2000-10-12 | 2004-03-09 | 17000 |
| 5 | 12 | TestCom | 2010-10-10 | 2014-10-10 | 35000 |
+---+---------+-----------+--------------+--------------+-------+
I want to display like this
+------------+--------------+------------------+
| User Name | Last Company | Last Drawn Salary|
+------------+--------------+------------------+
| ABCD XMHE | Company 3 | 23000 |
| LDEF LMOP | TestCom | 35000 |
+------------+--------------+------------------+
My query is like this
SELECT employee.id AS eid, employee.employer_id, employee.fname, employee.lname, emp_details.id as emid, emp_details.emp_id, emp_details.company, emp_details.joining_date, emp_details.leaving_date, emp_details.last_drawn_salary
FROM employee
INNER JOIN emp_details ON employee.id = emp_details.emp_id
WHERE employee.pan='".$pan."'
GROUP BY emp_details.emp_id
ORDER BY emp_details.id DESC
I also tried using like this max(emp_details.id) as emid but is still shows the 1st inserted column only. whats the problem here?
POST HTML Form data to SQL with PHP [duplicate]
This question already has an answer here:
I have searched an all answers I found I can't get to work.
I am trying to create a form that adds a row to my database.
add.html
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Street Form</title>
</head>
<body>
<form onSubmit="php/insert.php" method="post">
<p>
<label for="streetname">Street Name:</label>
<input type="text" name="streetname" id="streetname">
</p>
<input type="submit" value="Submit">
</form>
</body>
</html>
insert.php
<?php
include 'php/connect.php
// attempt insert query execution
$sql = "INSERT INTO streets (name) VALUES ($_POST['streetname'])";
mysql_query($sql);
// close connection
mysqli_close($link);
?>
I know my connect.php works because I used it to display a random row from my DB
I'm still VERY new to SQL so I know it's a simple fix but not sure where.
I'd love a form that could add 5 values at once from 5 input fields but that is further down the road
Wrapping a prepared statement in a function
I've been reading articles about SQL Injection, and decided to modify my code to prevent SQL injection.
For example, I have an input which I insert the value to my database. Initially, my guard against injection was this:
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
// $data = addslashes($data);
$data = mysql_real_escape_string($data);
return $data;
}
$artist = $_POST["artist"]; // can be anything
$artist = test_input($artist); // escaped chars are &, quotes, <, >, n, r, etc.
if ($mysqli->query("SELECT * FROM `my_table` WHERE `artist` = '$artist'")->num_rows == 0) {
$mysqli->query("INSERT INTO my_table (artist) VALUES ('$artist')");
echo "New artist is added.";
} else {
echo "Artist already exists.";
}
In the articles I've read, it was suggested that one should use prepared statements. I've changed my code and used that:
$artist = $_POST["artist"]; // can be anything
$query = $mysqli->prepare("SELECT * FROM my_table WHERE artist = ?");
$query->bind_param("s", $artist);
$query->execute();
$result = $query->get_result();
$query->close();
if ($result->num_rows == 0) {
echo "Artist doesn't exist in the DB." . PHP_EOL;
$query = $mysqli->prepare("INSERT INTO my_table (artist) VALUES (?)");
$query->bind_param("s", $artist);
$query->execute();
if ($query->affected_rows > 0) {
echo "Artist is added to the DB." . PHP_EOL;
}
$query->close();
} else {
echo "Artist already exists in the DB." . PHP_EOL;
}
While this prevents SQL injection, it doesn't do anything about XSS. So I decided to modify test_input (removed $data = mysql_real_escape_string($data);) and use it to prevent script injection.
function test_input($data) {
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}
$artist = $_POST["artist"]; // can be anything
$artist = test_input($artist);
Now, my problem is about using prepared statements. I'll be inserting three items; artist, album, and song. Repeating the same process (prepare, bind, execute, close) over and over again seems redundent to me. I want to create a function and wrap the prepared statement process with it. Something like this:
function p_statement($mysqli, $query_string = "", $type = "", $vars = []) {
$query = $mysqli->prepare($query_string);
$query->bind_param($type, $vars);
$query->execute();
$result = null;
preg_match("/^[A-Z]+/", $query_string, $command);
switch ($command[0]) {
case "SELECT":
$result = $query->get_result();
case "INSERT":
$result = $query->affected_rows;
}
$query->close();
return $result;
}
Though, this presents a problem: $vars array. Since the number of variables that'll be passed to mysqli_stmt::bind_param() will be variable/dynamic, I've used an array in the main function p_statement. I don't know how I should the pass the items in the array to the mysqli_stmt::bind_param(). bind_param expects (type, var1, var2, varn,), and I've got an array.
How can I make this work?
decimal round wrong calculation
I have the sample following numbers which are stored in an mysql db in the decimal(10,2) format
1499.3927125506 - 1499.39 -> this is how it saved into the database
384.41295546559 - 384.41
278.74493927126 - 278.74
537.44939271255 - 537.45
The actual total before saving into the database is 1700, however after the saving the total becomes 1699.99
How can I make the total 1700 NOT 1699.99?
Reverse initial order of SELECT statement
I want to run a SQL query in Postgres that is exactly the reverse of the one that you'd get by just running the initial query without an order by clause.
So if your query was:
SELECT * FROM users
Then
SELECT * FROM users ORDER BY <something here to make it exactly the reverse of before>
Would it just be this?
ORDER BY Desc
modify format of multiple database columns at once
I have imported a csv table into sql db using phpmyadmin. I guess the default format is decimal(8,5), or at least that is how it comes out. Seems overkill and I thought I could reduce to 4,1. Problem is there are around 470 fields. I know how to change one at a time but this would take a long time. Is there a faster way?
Pulling SQL Server GEOGRAPHY to POLYGONS
I have this query
SELECT
' {"location":"'+ G.ZCTA5CE10 +', ' + INTPTLON10 + ', ' + INTPTLAT10 + '",'+
'"polygon":' +
replace(replace(replace(replace(replace(replace(replace(G.geom.ToString(), 'POLYGON ((', '[{"lng":'), '))', '}]'), ', ', '},{"lng":'), ' ', ',"lat":'), ')', ''), '(', ''), 'MULTI', '') +
'}'
FROM REF_ZIP_GEOG G WITH(INDEX([geog_sidx]))
WHERE G.geom.STDistance(geography::STPointFromText('POINT(-81.3225 32.113)', 4326))<= 40234; --40.234 KM ~ 25 Miles
The intent of this query is to "Stringify" the GEOGRAPHY polygon into google maps. POLYGONS are working great. However, if the POLYGON is a MULTI-POLYGON, I am having issues working the JSON. The result is a Google error of a bad JSON.
Has anyone worked with MULTI POLYGONS and could you recommend anything to alter my SQL statement to work this correctly?
Thanks.
Subquery in IN does not work
I'm using TransactSQL (Microsoft SQL Server 2014) and here's my problem:
I'm using the standard "Northwind" database for practice and I wanted to see a list of Categories that don't have any Products.
Firstly I created a new Category, which wouldn't have any Products:
insert into Categories (CategoryName) values ('TestCategory')
Then I wrote this:
SELECT CategoryName
FROM Categories
WHERE CategoryID NOT IN (select CategoryID from Products)
Unfortunately it gives nothing.
Here's how I understand it:
I want to find categories without products connected with them, so I used
WHERE CategoryID NOT IN (select CategoryID from Products), because I want to compare every CategoryID from Categories table with a list which I get from select CategoryID from Products - this list includes every category that HAS products. So I think that using NOT IN should let me to see categories that don't have any products.
I hope you understood what I was trying to describe. It would be great if you could help.
Change(Put) data in ASP.NET WebApi server from AngularJS client can't change in SQL DataBase
I'm working in a project where i put data in restaurant (table in DataBase)
but nothing change in Sql DataBase
,The UI of the project in asp.net
My Html file
<tr>
<td>
<asp:TextBox ID="TextBox1" runat="server" name="rest" placeholder="Write the restaurant ID" ng-model="resturantid" required></asp:TextBox>
</td>
<td>
<input type="button" value="Find" class="btn btn-primary-outline" ng-click="getRestaurantById(resturantid)" />
</td>
</tr>
<tr>
<th>Restaurant Name</th>
<td>
<asp:TextBox ID="TextBox2" runat="server" ng-model="res.R_Name"
required>{{res.R_Name}} </asp:TextBox>
</td>
</tr>
<tr>
<td>
<input type="button" class="btn btn-primary-outline" ng-click="toggleEdit(res.R_ID,res)" formmethod="put" value="Edit" />
</td>
<td></td>
</tr>
</table>
My Script
app.controller("editownercontroller", ['$scope', '$http', function ($scope, $http) {
///////////////////edit profile/////////////////////////////////
$scope.Flag = 0;
$scope.getRestaurantById = function (id) {
$http.get("http://localhost:10566/api/Restaurants/" + id).then(function (response) {
if (response) {
$scope.res = response.data;
}
}
)
};
$scope.getData = function (id,Restaurant) {
getcuisine();
toggleEdit(id, Restaurant);
}
$scope.getcuisine = function () {
$http.get("http://localhost:10566/api/Cuisines").then(function (response) {
if (response) {
$scope.cuisines = response.data;
}
});
};
$scope.updatecuisine = function (id, cuisine) {
$http.put("http://localhost:10566/api/Cuisines/" + id, cuisine).then(function () {
});
}
$scope.toggleEdit = function (id, new_res) {
$http.put("http://localhost:10566/api/Restaurants/" + id, new_res).then(function () {
});
}
,And in the Web API Server
// PUT: api/Restaurants/5
[ResponseType(typeof(void))]
public IHttpActionResult PutRestaurant(int id, Restaurant restaurant)
{
if (!ModelState.IsValid)
{
return BadRequest(ModelState);
}
if (id != restaurant.R_ID)
{
return BadRequest();
}
db.Entry(restaurant).State = EntityState.Modified;
try
{
db.SaveChanges();
}
catch (DbUpdateConcurrencyException)
{
if (!RestaurantExists(id))
{
return NotFound();
}
else
{
throw;
}
}
return StatusCode(HttpStatusCode.NoContent);
}
}]);
MySQL: unique informations cross 2 tables
I have 2 tables battles & battle_user
structure:
battles table:
id create_date
1 2015/...
battle_user table:
id battle_id user_id
1 1 1
2 1 2
Only 2 users accepted in each battle, the question is: Is there a way using primary/foreign keys (or indexing) to prevent insert another battle for same 2 users?
prevent this:
battles table:
id create_date
1 2015/...
2 2015/...
battle_user table:
id battle_id user_id
1 1 1
2 1 2
3 2 1
4 2 2
prevent create 2 battles between same 2 users
Update query having inner query referencing same table
update goods_inward_outward
set is_active = 0
where gi_go_id IN (select gi_go_id from goods_inward_outward where gi_go_id <100)
I m getting following error
1093 - You can't specify target table 'goods_inward_outward' for update in FROM clause
Whats is the problem in above ?
Is this because i am using update and select query on same table?
If so, what can be alternative solving ?
Thanks
SQL Pivot no aggregate
I am looking to pivot a data set result out but there is no aggregate that is happening.
Select StufferID from from #temp_Stuffers where SponsorID IN (111,222,333)
This is going to give me 0 - 2 results. How can I use pivot to make it
Sponsor ID StufferID1 StufferID2
111 S1 S2
222 S5
333
Rather than
SponsorID StufferID
111 S1
111 S2
222 S5
how to take a value less than 0 if recorded obtained like this?
select c.location_id as id_gudang, c.location_name as nama_gudang, a.item_code as id_barang, a.item_name as nama_barang, sum(b.item_qty) as qty
from t_inventory as a
join t_inventory_transaction as b on a.item_id = b.item_id
join t_site_location as c on b.whse_id = c.location_id
group by a.item_code
this data from query
enter image description here
Converting integer milliseconds value to date time format in sql server
I have a column called start time which is populated with a number like "1465815600000" and increasing. This column is the number of milliseconds from the year 1970 jan 1st 12.00:00:000 AM to a certain date in june 2016. This is in integer, which I need to convert it to date time format.
EX: 1465815600000 => 2016-06-12-12:00:00 (something like this)
Can somebody help me with to write this function?
WooCommerce: Finding the products in database
I'm creating a website using WooCommerce and I want to restrict the available products to users depending on the postcode that they enter in the search form on my home page.
To be able to achieve that I'll have to specify the conditions of each product within the database in phpMyAdmin, but I can't seem to find it.
Does anybody know where the woocommerce database for products and/or categories are within phpmyAdmin?
Thank you in advance.
Regex for SQL Server Name, Database Name , SQL Login and Password
We are creating one web application in which one page has data entry form for SQL Server. Here user needs to enter data for SQL Server Name, Database Name and UserName and Password for SQL Authentication.
I have referred https://msdn.microsoft.com/en-us/library/ms175874.aspx link for valid characters allowed. But I want regex for all fields SQL Server Name, Database Name and UserName and Password for SQL Authentication so that I can validate their values.
(MySQL) Create a function that accepts an item Id as input and returns the retail price of that item. Name the function itemPrice
I have to create a function called itemPrice that accepts an item id as an input and returns the retail price.
I will attach a link to the screen shot of the table I think I should be working from.
I am not really sure where to start.
MySQL - sort result by specific record
Perhaps someone could tell me, how to order MySQL output in this specific scenario:
I have table like this:
| id | Value1 | Value2 | ...More values that doesn`t matter in this example
^-----^--------^--------^
| 1 | 1 | X |
| 2 | 2 | X |
| 3 | 3 | 2 |
| 4 | 1 | X |
| 5 | 2 | X |
| 6 | 3 | 3 |
| 7 | 1 | X |
| 8 | 2 | X |
| 9 | 3 | 1 |
I want to get values from this table, and I want to order them by Value2, but only there, where Value1 is 3 (X values doesn't matter).
What`s the best way to do this, with good performance?
Thanks in advance!
Wordpress: Restoring a theme to it's default settings
So I installed the "twenty fourteen" theme for my Wordpress website, and I want to change it to it's deault settings. With this I mean resetting the theme to how it was when first installed (with no modifications).
Currently I have checked the database to check if I could restore the wp-options theme value to default, but that item on the database does not exist. I also tried changing themes, deleting twenty "fourteen" and installing again from wordpress and still no luck.
Any ideas?
How to convert a number(Integer) into years,months,days format in oracle Select query?
For example: I have a select query which i will get no. of non null records on a column x.
Let it be count(x) = 35. As per my requirement i need to display the value as 1m,5d.
If 369 days then 1y4d(Here actually i am leaving .25 days but if possible should not neglect. Also should consider leap year situations)
If 400 days then 1y1m5d
(same rule appliable as above)
Please let me know if you have a solution. Thanks in advance.
How can i get table name as column from the current table in sql server
I'm trying to find a way to get the "table name" as column from the current table in SQL Server 2012
SELECT
'School1.dbo.Person', Age, COUNT(Age)
FROM
School1.dbo.Person
GROUP BY
Age
UNION ALL
SELECT
'School2.dbo.Person', Age, COUNT(Age)
FROM
School2.dbo.Person
GROUP BY
Age
As I'm hard-coding the table name in first column of each select statement, it doesn't make much sense, is there way I could get the table name in first column dynamically?
Appreciate your thoughts!
RAP
vendredi 24 juin 2016
SQL Export after 100 entries and then Delete the Table entries entirely
this is my first post.
I have insert form using pdo, ajax and sql and I Want to after 100 INSERT export to csv in back up folder . And then purge the table of all information.
I need help to understand how I can do this?
I was thinking a trigger or event or maybe C# program to do this but i wanted opinions on all things security, functionality and different ideas to make this happen.
I truly do appreciate any time spent considering my request. . .
Thank you
MySQL - Group orders count by clients numbers
I want to group order's count to show how many clients have that number of orders.
I have come up with:
select count(*) as quantidade_pedidos, clientes.id
from pedidos
inner join clientes
on pedidos.cliente_id = clientes.id
where pedidos.aprovado = 1
group by quantidade_pedidos
but I just can't group by 'quantidade_pedidos' anyway.
Is there any way to group by a temporary column? Another way of doing this? show how many clients (number) have that number of orders placed?
Example
8 orders placed -> 3 clients have 8 orders placed
etc
Updating data in mysql database error [on hold]
I have a problem when I try to update data in MySQL database.
There is no syntax error or something like that.
The code is totally correct.
<?php
$id = $_REQUEST['id'];
$newname = $_REQUEST['newname'];
$newemail = $_REQUEST['newemail'];
$newpassword = $_REQUEST['newpassword'];
mysql_connect('localhost','root','root') or die('Connectuion failed');
mysql_select_db('test');
mysql_query("UPDATE users SET name='$newname', email='$newemail', password='$newpassword' WHERE id='$id'");
echo "<h1>Your acount have been updated succefully</h1>";
mysql_close();
?>
Storing lyrics in a mysql database with proper mapping
Something like.
I want to have a database where people can search Christian hymns based on hymn #.
A hymn has a title, chorus, and a couple verses, and some bible references. If my hymn table store id, title,chorus,verses,bible_refs. When a user search and find a hymn # and want view it, how can i be able to map the order in which the hymn appears. For example, Title, Verse1, Verse2, chorus, Verse3 and bible_ref. I am also want to achieve proper line breaks between the order. The hymns can structured in various orders. Any expert on php who did that kind of stuffs can help me I would appreciate.
How can I grouping an unix time per day?
I have a table like this:
// requests
+----+----------+-------------+
| id | id_user | unix_time |
+----+----------+-------------+
| 1 | 2353 | 1339412843 |
| 2 | 2353 | 1339412864 |
| 3 | 5462 | 1339412894 |
| 4 | 3422 | 1339412899 |
| 5 | 3422 | 1339412906 |
| 6 | 2353 | 1339412906 |
| 7 | 7785 | 1339412951 |
| 8 | 2353 | 1339413640 |
| 9 | 5462 | 1339413621 |
| 10 | 5462 | 1339414490 |
| 11 | 2353 | 1339414923 |
| 12 | 2353 | 1339419901 |
| 13 | 8007 | 1339424860 |
| 14 | 7785 | 1339424822 |
| 15 | 2353 | 1339424902 |
+----+----------+-------------+
I want to grouping unix_time column based on separated days. Actually I'm trying to make this for an specific user:
As you see I need tow numbers for an user:
the number of all days which there is a foot print of the user into requests table
the number of biggest consecutive days
How can I do that?
Actually I can use WHERE id_user = :id to select user's rows. And I can calculate the number of days by SUM(). And by using MAX() I can calculate the biggest consecutive range. Just I need to grouping those unix times.
Linux Backup MySQL failed when assign to crontab
Hi fellow StackOverFlow,
Today , I have a very weird scenario in my Unix machine.
I'm currently using this command manually to save my current database backup.
/usr/bin/mysqldump -u root -p'thetechnofreak' admin_test > /mnt/databasesql/admin$(date +%Y-%m-%d-%H.%M.%S).sql.gz
In my crontab , I access it using
crontab -e
Then I add the following to the list
30 2 * * * /usr/bin/mysqldump -u root -p'thetechnofreak' admin_test > /mnt/databasesql/admin$(date +%Y-%m-%d-%H.%M.%S).sql.gz
I realize that it's not doing it automatically, is there anything that i've missed?
Is there a flagging option or a logging method to know whether the backup is done successfully or not.
Thanks in advance.
I did error check on DataGrip but the sql file was still generating a syntax error when I try to run it
So I was installing a schema on my MySql database. I error checked the sql script on DataGrip, it shows no syntax error; yet when I runt it the syntax error pop up
ERROR 1064 (42000) at line 54: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the right syntax to use near '.ortholog_seq_a_ix on orthomcl.ortholog(sequence_id_a)' at line 1
The original code is as follow:
CREATE TABLE orthomcl.Ortholog (
SEQUENCE_ID_A VARCHAR(15),
SEQUENCE_ID_B VARCHAR(15),
TAXON_ID_A VARCHAR(15),
TAXON_ID_B VARCHAR(15),
UNNORMALIZED_SCORE DOUBLE,
NORMALIZED_SCORE DOUBLE
);
CREATE INDEX orthomcl.ortholog_seq_a_ix on orthomcl.ortholog(sequence_id_a);
By the way I'm using an ubuntu system.
SQL: Create new groups of Date Ranges from a Set of Date Ranges
I am trying to find an appropriate title for my question and so far it's been 30 minutes of trying... :)
So far I have the following example
DateFrom DateTo Amount
2015/01/01 2015/08/31 1$
2015/01/01 2015/12/31 3$
2015/08/01 2015/12/31 7$
For the first line we get that we have 0.125$/month (1$/8 months)
For the 2nd line we get that we have 0.25$/month (3$/12 months)
For the 3d line we get that we have 1.4$/month (7$/5 months)
Considering the above, we would like to create a new group of date ranges in order to have the sums of the amounts. Something like the results below:
DateFrom DateTo Amount
2015/01/01 2015/07/31 (0.125$+0.25$)*7 =2.625$
2015/08/01 2015/08/31 (1.4$+0.125$)*1 =1.775$
2015/09/01 2015/12/31 (1.4$+0.25$)*4 =6.6$
The sum of the above is 11$ just like the original data. The result we want is actually the sum of the amount per unique group of date ranges.
Is this possible to be achieved with SQL?
Mysql self join not giving accurate data
I have employee table like below.
emp_id emp_name emp_supv
4081 Nancy Brown NULL
4083 Peter Parker 4081
4055 Jacob Miller 4083
4058 Mary Ray 4083
4060 Jane Smith 4081
4061 Bob Hunter 4081
4066 Nancy Smith NULL
4061 Bob Smith 4066
I want LIKE query with key word Nancy and it should return result like below(parent record with name like Nancy and it's children records.).
4081 Nancy Brown NULL
4083 Peter Parker 4081
4060 Jane Smith 4081
4061 Bob Hunter 4081
4066 Nancy Smith NULL
4061 Bob Smith 4066
try with following query but it is return only child records not the parent record having emp_supv NULL
SELECT sa.id AS id,sa.name AS Name,
sa2.id AS child_id, sa2.name AS child_name
FROM employees AS sa LEFT OUTER JOIN employees AS sa2
ON sa.emp_supv = sa2.emp_id where sa2.emp_name LIKE '%Nancy%';
(OpenShift) Permission denied when installing my own cartridge from my github repository
I froked MySQL cartridge from official github repo and I added some modifications to support utf8mb4 encoding.
this is my cartiridge on github : https://github.com/amgohan/openshift-origin-cartridge-mysql-utf8mb4
When I want to add this cartridge to my scalable php app using the openshift web admin panel by providing https://raw.githubusercontent.com/amgohan/openshift-origin-cartridge-mysql-utf8mb4/master/metadata/manifest.yml. I get this error :
Shell command '/sbin/runuser -s /bin/sh 54474a6a500446ad2d0000ac -c "exec /usr/bin/runcon
'unconfined_u:system_r:openshift_t:s0:c5,c230' /bin/sh -c
"/var/lib/openshift/54474a6a500446ad2d0000ac/mysql-utf8mb4/bin/setup --version
5.5""' returned an error. rc=126
/bin/sh: /var/lib/openshift/54474a6a500446ad2d0000ac/mysql-utf8mb4/bin/setup: Permission denied
I don't know what's wrong, can anyone help ?
php array_walk_recursive not working on mysql result set array with stripslashes
I have a mysql result array and I'm trying to stripslashes on the array using array_walk. It's not stripping slashes from mysql. It is working on the array I manually added ($dataArr['xxx']) though.
Here is my code:
$sql = ' select * from `ads` where id = 3 ';
$res = mysql_query($sql, $conn) or die(mysql_error());
$row = MYSQL_FETCH_ASSOC($res);
$dataArr = $row;
$dataArr['xxx'] = '<script type='text/javascript'><!--//<![CDATA[
var m3_u = (location.protocol=='https:'?'https://ads.test.com/www/delivery/ajs.php':'http://ads.test.com/www/delivery/ajs.php');
var m3_r = Math.floor(Math.random()*99999999999);
if (!document.MAX_used) document.MAX_used = ',' etc.... etc....;
';
array_walk_recursive($dataArr, 'stripslashes');
print '<pre>'; print_r($dataArr); print '</pre>';
Insert data in SQL table having primary and foreign key in the same table [duplicate]
This question already has an answer here:
can foreign key refer to primary key in same table?
2 answers
I have one table having two columns ID1 and ID2. ID1 is primary key and ID2 is foreign key of ID1 column. The problem is user wants to insert data in table given below
ID1 (PK) ID2 (FK)
1 2
2 3
3 4
4 1
Is it possible, one is to using update is there any other way
FK is not null.
and need to insert all the data in one sql query.
The link provided for the same question , i already visit that link . In that question FK is null and they don't insert all record in one sql query.
Over to all this what i say is PK =1 and same time FK=2 , which is not possible what i have read in books. So this question is totally different from above question.
How can i make every <p> tag clickable on a pop-up by using php?
as you can see my code is working with sql. Every email name
will be shown on a "pop up". And all of them will have separated p tag. What i am trying to make is when users click this p tag they must access another page. I am new to php and did something wrong . Some help would be great. Thanks.
<div id="dialog" title="Following">
<?php
$x=0;
$arrayName = array();
$sqls = "SELECT * FROM follow WHERE member_email='$email'";
$result = mysqli_query($conn,$sqls);
while($row = mysqli_fetch_assoc($result)) {
$arrayName[$x] = $row["person_email"];
$x=$x+1;
} ?>
<?php for($k = 0; $k < $x; $k++) {?>
<p id="pop" value="$arrayName[$k]" onclick="popFunc(this.value)"><?php echo $arrayName[$k]; ?></p>
</div>
/////////////////////////////////////////////////////////////////
function popFunc($element){
$_SESSION['visiter']=$element;
document.location.href = 'http://localhost/example/visiter.php';
}
Find the distance between greater than 100K locations
I have two MySQL tables with locations, table1 and table2 (see below). There are > 100K rows in each table. I'd like to find the distance between every location in these two tables using their geolocations.
Here is the MySQL query to find the distance between a single geolocation, e.g, (-37.22, 88.88) and all the locations in table1.
$lat = -37.22;
$long = 88.88;
SELECT id, latitude, longitude, name
((2 * 3960 *
ATAN2(
SQRT(
POWER(SIN((RADIANS($lat - latitude))/2), 2) +
COS(RADIANS(latitude)) *
COS(RADIANS($long)) *
POWER(SIN((RADIANS($long - longitude))/2), 2)
),
SQRT(1-(
POWER(SIN((RADIANS($lat - latitude))/2), 2) +
COS(RADIANS(latitude)) *
COS(RADIANS($long)) *
POWER(SIN((RADIANS($long - longitude))/2), 2)
))
)
)) AS distance FROM table1 ORDER BY distance;
Table1
id name latitude longitude
1 foo1 -37.12 62.34
2 foo2 -47.12 72.34
3 foo3 -57.12 82.34
Table2
id name latitude longitude
1 bar1 -38.22 66.11
2 bar2 -48.22 76.11
3 bar3 -58.22 86.11
Given that this is also a big data, I'm not sure where to start. Thoughts?
rake db:schema:dump is not producing schema for all databases
I have this project and I need to integrate a bunch of databases into my Rails project.
This is the database configuration that I have to work with.
$ nano config/database.yml
production:
adapter: mysql2
reconnect: true
pool: 5
username: user_xyz
password: 123456
database: database1
host: localhost
database_2:
adapter: mysql2
reconnect: false
pool: 5
username: user_xyz
password: 123456
database: database2
host: 192.168.2.100
database_3:
adapter: mysql2
reconnect: false
pool: 5
username: user_xyz
password: 123456
database: database3
host: 192.168.2.101
database_4:
adapter: mysql2
reconnect: false
database: database4
pool: 5
username: user_xyz
password: 123456
host: 192.168.2.102
I need to update the schema inside db/schema.rb but unfortunately it's only producing the schema for production (database1).
$ RAILS_ENV=production bundle exec rake db:schema:dump
I can't just run this as it complains:
$ bundle exec rake db:schema:dump
rake aborted!
database configuration does not specify adapter
What do I need to do to get all of these databases into db/schema.rb?
How to configure PHP 7 - Apache with MySQL PDO driver in Debian Docker image?
I'm using official docker images for PHP 7 (7.0.3-Apache) and MySql (5.7.10).
Using docker-compose, created containers from images and linked both.
Copied php.ini from https://github.com/php/php-src/blob/php-7.0.3/php.ini-production, replaced dll extensions with so and placed that file in /usr/local/etc/php and enabled _pdo_mysql_ extension in it.
extension=php_pdo_mysql.so
phpinfo shows php.ini loaded but not pdo_mysql extension because it's not installed.
I googled and tried different extension names with apt-get install:
php-mysql, php7-mysql, php7.0-mysql, php7.0.3-mysql.
None of them works. Error says: E: Unable to locate package.
With php5-mysql, it's get installed but after restarting apache with command: docker kill --signal="USR1" <container-name>, extension doesn't show loaded in php.ini.
(Don't think it's much related to docker but I'm new to docker and trying with that now, so mentioning it here.)
Can anyone help to configure pdo_mysql extension with php7-Apache?
Parallel Development and Live Site Management
I have a web project that has the following requirements/features:
Front end in HTML CSS and JS/JQuery
Back end in PHP with a MySQL database.
Uses external resources via an API (Stripe in this case, who have parallel test and live interfaces).
Development is currently done in Netbeans
Source Control is managed through Netbeans into a Git repository on VS Team Services online, which we are also using for development on our Windows product.
The live site is hosted on a Linux server at a shared hosting provider, files are transferred via FTPES. When the product demand grows we will transfer the project to a dedicated off-site server.
Once we go live we need to have a reliable, manageable way of continuing development in isolation, then replicating the changes to the production area once the changes are tested and validated.
What are the tools, methods and/or best practices available for managing parallel development and live website/web-app projects?
get value mysql count and save to table
What i have here is to get the value of my count and save to my table but it's not working. I just want to get the count value, can you help me ? i will appreciate any answer. No problem with displaying with count but only in saving in my table.
<form method="POST">
Date : <input type="text" value="<?=$p_date?>" name="datereceived" />
<input type="submit" name="save" value="save">
</form>
**PHP**
<?
$sql="SELECT allinvty3.*, barcode.* , sample.*, count(barcode.itemcode) as icount from barcode
INNER JOIN sample on barcode.itemcode = sample.in_code
INNER JOIN allinvty3 on barcode.itemcode = allinvty3.in_code
where barcode.refnumber='$temp' group by barcode.itemcode";
$result = $conn->query($sql);
while($row = $result->fetch_assoc()) {
$icount = $row['icount'];
}
if(isset($_POST['save'])){
$sql = "UPDATE barcode SET
datereceived ='$p_date' , actualacount ='".$icount."' ,
status='COMPLETE' WHERE status='PENDING'";
$conn->query($sql) ;
}
?>
How do I store the grepped result to variable if the result has multiple lines
I have a command in my script that goes like this
MESSAGE=`grep -Po 'MSG.K[ws,.''',.:]*' < $LINE`
Now when this command is run I have an output which look like this
Kohl's EDI will be down for scheduled maintenance starting at 12:30 am until
approximately 4:00 am central time on Wednesday June 22nd. Kohl's will not be
able to send or receive EDI or AS2 transmissions during this time. If your
company's AS2 software has an automated process to resend a file after a
failure, Kohl's encourages your company to enable the resend process. This is
also a reminder for AS2 trading partners that Kohl's AS2 certificate will be
changing at 11:00 am central time on Tuesday June 21st.
Now after grepping the whole thing out I would pass the result of the command to a variable that will be used so that I can store the result to a mysql database
The question is How will I do it?
Fuzzy logic matching
So, I'm looking at implementing Fuzzy logic matching in my company and having trouble getting good results. For starters, I'm trying to match up Company names with those on a list supplied by other companies.
My first attempt was to use soundex, but it looks like soundex only compares the first few sounds in the company name, so longer company names were too easily confused for one another.
I'm now working on my second attempt using the levenstein distance comparison. It looks promising, especially if I remove the punctuation first. However, I'm still having trouble finding duplicates without too many false positives.
One of the issues I have is companies such as widgetsco vs widgets inc. So, if I compare the substring of the length of the shorter name, I also pickup things like BBC University and CBC University campus. I suspect that a score using a combination of distance and longest common substring may be the solution.
Has anyone managed to build an algorithm that does such a matching with limited false positives?
How can i include null values for joined tables with where
How can i modify my query to include the vocation code, even if there was no associated results for that code?
code should be displayed ( i have 6 vocation codes) and count should be displayed as 0 if there were no entries for that code.
$maori = $request->exists('maori');
$years = collect([2011,2012,2013,2014,2015,2016]);
$providers = $years->map(function($year) use ($maori){
$query = DB::table('vocations')
->selectRaw('vocations.*,count(*) as count')
->leftjoin('standard_vocation', 'vocations.id','=','standard_vocation.vocation_id')
->leftjoin('standards','standard_vocation.standard_id','=','standards.id')
->leftjoin('results','standards.id','=','results.id')
->leftjoin('learner_provider','results.learner_provider_id','=','learner_provider.id');
if($maori) {
$query->join('learners','learner_provider.learner_id','=','learners.id')
->join('learner_ethnicity','learners.id','=','learner_ethnicity.learner_id')
->leftjoin('ethnicities','ethnicities.id','=','learner_ethnicity.ethnicity_id')
->where('ethnicities.code','211');
}
$query->where('learner_provider.year',$year);
$query->groupBy('vocations.id');
return $query->get();
});
How to get combinations of items with a condition / Limit?
I'm trying to make a combination list. I have this test table.
ID Item Stock Price
A1 Apple 20 250
A2 Grape 50 500
A3 Lychee 0 650
A4 Orange 150 550
A5 Date 5 850
Let's say I set a limit of 10000. This means the SELECT query have a condition like this. (WHERE Total > 0 AND Total <= 10000)
The result example (ignore the spacing):
Combination Item Purchase Total
T001 Apple 10 2500 //This is APPROVED because the total is under 10000
T002 Apple 5 1250 //This T002 has two items. The Total of T002 is 6250
T002 Grape 10 5000 //So, It is APPROVED
T003 Lychee 50000 32500000 //Because the Stock of Lychee is 0, This is REJECTED
T004 Date 10 8500 //Because the Purchase > Stock (10 > 5), This is REJECTED
T005 Orange 5 2750 //This T005 has two items.
T005 Date 1 850 //The Total of T005 is 16100.
T005 Apple 50 12500 //So, It is REJECTED
nb:
The Limit (in this example is 10000) is set by user.
The Purchase is any integer > 0. (or should I limit it to 10000 > Purchase > 0 )?
The REJECTED item is SHOULD NOT shown in the list.
Is it possible to make the list from MySQL query only? Any help and suggestions are greatly appreciated. Thank You so much.
How do I select all id's that are present in one table but not in another
I am trying to get a list of department Ids are present in one table, (PS_Y_FORM_HIRE), but which don't exist in another table (PS_DEPARTMENT_VW).
Here is the basics of what I have which isn't working:
SELECT h.DEPTID FROM PS_Y_FORM_HIRE h, PS_DEPARTMENT_VW d WHERE NOT EXISTS (
SELECT d1.DEPTID FROM PS_DEPARTMENT_VW d1 WHERE d1.DEPTID = h.DEPTID
and d1.SETID_GL_DEPT = 'IDBYU'
);
I'm trying to form this query in SQL Developer, but it just returns a long list of blanks (after spinning/running the query for a very long time).
In addition, I need this to be effective dated, so that it only grabs the correct effective-dated row, but I was unsure how and where to incorporate this into the query.
Also note that DEPTID isn't a key on PS_Y_FORM_HIRE, but is on PS_DEPARTMENT_VW. (Along with SETID_GL_DEPT and EFFDT).
So again, ideally, I will have a list of all the department ids that appear in PS_Y_FORM_HIRE, but which are not in PS_DEPARTMENT_VW.
Oracle SQL output wrong
I am not being able to produce the desired results of this question:
Display alphabetically the full name, job, salary, department number for each employee who earns less than the best paid unionized employee
unionized employees are not managers presidents of vice presidents
And do not work in SALES or MARKETING departments
Full name should be displayed as Firstname Lastname and should have the heading Employee.
Salary should be left-padded with the = symbol till the width of 12 characters. It should have an alias Salary.
salary is formatted as a currency amount incl. thousand separator, but no decimals
Limit the width of the Employees name to 25 characters.
The output lines should look like this sample line:
Jonathon Taylor SA_REP ==== $8,600 80
My query so far:
select last_name || ', ' || first_name "full name", job_id, salary, department_id
from employees
where salary < ANY
(SELECT MAX(salary)
from employees e join departments d
where e.job_id <> 'AC_MGR' OR 'AD_VP' OR 'AD_PRES'
AND d.department_name <> 'SALES' OR 'MARKETING'
GROUP BY department_id)
If someone can help me out and point me in the right direction of how to get the proper output, that would be greatly appreciated. Thank you!
jeudi 23 juin 2016
TSQLQuery and update of data aware components
I have a sqlite database containing 'notes' for customers. The database design is quite simple and involves one table for notes, a "link" table, linking, relating customers and notes named customer_note and a customers table. The notes table have an id field, the customer_note have customer_id and note_id (one to many) and the customers table have a id as well.
The query to obtain the notes for a selected customer is:
SELECT * FROM note n
INNER JOIN customer_note cn
ON (cn.note_id = n.id)
WHERE customer_id = :customerID
ORDER BY created_on ASC
The query is executed in an event where the customers client dataset is scrolled, i.e.
customersCDSAfterScroll()
{
int cID = customerCDS->FieldByName("id")->AsInteger;
customerNotesQ->Params->ParamByName("customerID")->AsInteger = bID;
customerNotesQ->Open();
//Get notes
string note = stdstr(customerNotesQ->FieldByName("note")->AsString);
Log(lInfo) << "Note is: "<<note;
customerNotesQ->Close();
}
The query is referenced by a DataSetProvider, a ClientDataSet and a DataSource component. On the UI a TDBLookupListbox is receiving the data.
Problem is, the TDBLookpListbox shows ALL notes for all customers.
In the log messages, from the code above, I can see that the query seem to do its job correctly however.
Any idea on what is going wrong?
C# Insert Into syntax error
I am trying to insert data into a table in MS Access. I keep getting the error Missing semicolon (;) at end of SQL statement. or a different error saying that i my Insert query needs to have a value or table in it. Here is my code
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace MiddleWare
{
public partial class Sales : Form
{
public Sales()
{
InitializeComponent();
}
private void btnUpdate_Click(object sender, EventArgs e)
{
int empId = int.Parse(txtEmpID.Text);
string cmdText = @"INSERT INTO [Sales]
([Printers], [Ink], [Paper])
VALUES (@Printers,@Ink,@Paper)
SELECT @EmpID FROM (Emplopyee)";
using (OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\cp-stud-nas1usersmat72462DocumentsSalesData.accdb"))
using (OleDbCommand cmd = new OleDbCommand(cmdText, con))
{
con.Open();
cmd.Parameters.AddWithValue("@Printers", OleDbType.VarWChar).Value = txtPrinters.Text;
cmd.Parameters.AddWithValue("@Ink", OleDbType.VarWChar).Value = txtInk.Text;
cmd.Parameters.AddWithValue("@Paper", OleDbType.VarWChar).Value = txtPaper.Text;
cmd.Parameters.AddWithValue("@EmpID", OleDbType.VarWChar).Value = txtEmpID.Text;
cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@EmpID", txtEmpID.Text);
cmd.CommandText = "SELECT [Total Sales] FROM Sales WHERE EmpID=@EmpID";
string result = cmd.ExecuteScalar().ToString();
MessageBox.Show(result);
}
}
private void Sales_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'DataSet1.DataTable1' table. You can move, or remove it, as needed.
this.DataTable1TableAdapter.Fill(this.DataSet1.DataTable1);
}
private void btnReport_Click(object sender, EventArgs e)
{
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=\cp-stud-nas1usersmat72462DocumentsSalesData.accdb");
{
this.DataTable1TableAdapter.Fill(this.DataSet1.DataTable1);
this.reportViewer1.RefreshReport();
}
}
}
}
How to insert and update value into database table php
I am beginner in PHP and i want to insert value into my database table for my drop-down value using PHP insert query, I am having an error to insert data and also want to update the existence value of database table when insert new value into database table, here is my code help me to solve it, My Code is below :
<?php
include 'dbconfig.php';
?>
<html>
<head>
<title>Sales Panel App</title>
</head>
<body>
<form method="POST" name="myForm" action="<?php $_PHP_SELF ?>">
<table border="1">
<tr>
<td>Sales Person List</td>
<td>
<?php
$sql="select name,id from sales order by name";
echo "<select name = 'salelist' value=''>Sales Person Name</option>";
echo "<option value = 'Select Sales Person' selected>Select Sales Person</option>";
{
foreach ($conn->query($sql) as $row){
echo "<option value=$row[id]>$row[name]</option>";
}
echo "</select>";
}
echo "<br>";
?>
</td>
</tr>
<tr>
<td>No of Panels</td>
<td><input type="text" name="panelnumber" size="1"></td>
</tr>
</table>
<p><input type="submit" value="Submit" name="submit" id="submitdata"></p>
</form>
<?php
$salelist = $_POST['salelist'];
$panelnumber = $_POST['panelnumber'];
$sql2 = "select * from sales where name = $salelist";
$result = mysqli_query($sql2);
if ($row = mysqli_fetch_array($result)) {
$oldvalue = $panelnumber;
$newvalue = $oldvalue + $panelnumber;
$query = 'update sales where name = $panelnumber';
}
else {
$qry = "insert into sales (id, name, panelnumber) values ('', $salelist, $panelnumber)";
}
?>
</body>
</html>
How to correct the "Balance" per transaction. Group by Date ASC
Good day, I need your help guys so that I can generate the correct data in "BALANCE" column for every transaction in "ASCENDING" order of the transaction date.
SELECT
t.TR_DATE
t.DEBIT,
t.CREDIT,
@balance := @balance + t.DEBIT - t.CREDIT AS BALANCE
FROM
(
SELECT s.TR_DATE AS TR_DATE,
SUM(IF(s.AMT>0 AND s.SLE_CODE=11,s.AMT,0)) AS DEBIT,
SUM(IF(s.AMT<0 AND s.SLE_CODE=11,s.AMT,0)) * -1 AS CREDIT
FROM
sldtl AS s
LEFT JOIN
transtype
ON
transtype.TransTypeID = s.TR_CODE
WHERE
s.SL_BRCODE= 1 AND s.SL_CLIENTID= 267 AND s.SLC_CODE= 13 AND s.SLT_CODE= 15 AND
s.REF_NO= s.REF_NO AND s.TR_DATE <= CURDATE()
GROUP BY
s.TR_DATE DESC, s.TR_CODE, s.TR_CTLNO
) AS t , (SELECT @balance := 0) var;
Result of the query above with "DESC" order of the transaction date
Accounts Payable
**Note in this Accounts Debit is a deduction.
Date of Deposit: 2011-12-31
Initial amount 79,799.44 as of 2011-12-31
+------------+-------------+---------------+-------------+
| Trans Date | Debit | Credit | Balance |
+------------+-------------+---------------+-------------+
| 2011-12-31 | 0.00 | 79,799.44 | 79,799.44 |->First Trans date
| 2012-01-15 | 0.00 | 560.27 | 80,359.71 |
| 2012-01-31 | 0.00 | 500.00 | 80,859.71 |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| 2016-03-15 | 0.00 | 1,000.00 | 92,218.37 |
| 2016-03-30 | 0.00 | 1,000.00 | 93,218.37 |
| 2016-04-06 | 30,000.00 | 0.00 | 63,218.37 |->Last Trans date
+------------+-------------+---------------+-------------+
When I replace the "DESC" to "ASC" the balance will become the amount of the last transaction. like debit=30,000, balance=30,000 also.
"ASC" order of transaction date. Needed Result.
Accounts Payable
**Note in this Accounts Debit is a deduction.
Date of Deposit: 2011-12-31
Initial amount 79,799.44 as of 2011-12-31
+------------+-------------+---------------+-------------+
| Trans Date | Debit | Credit | Balance |
+------------+-------------+---------------+-------------+
| 2016-04-06 | 30,000.00 | 0.00 | 63,218.37 |->Last Trans date
| 2016-03-30 | 0.00 | 1,000.00 | 93,218.37 |
| 2016-03-15 | 0.00 | 1,000.00 | 92,218.37 |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| .......... | ........ | ........... | ........... |
| 2012-01-31 | 0.00 | 500.00 | 80,859.71 |
| 2012-01-15 | 0.00 | 560.27 | 80,359.71 |
| 2011-12-31 | 0.00 | 79,799.44 | 79,799.44 |->First Trans date
+------------+-------------+---------------+-------------+
Edited with TR_DATE
Combining certain strings in an array based on positive matches against mySQL table
I have a multidimensional array composed of strings of various length as follow:
$a =
Array ( [0]
Array ( [0] AQWER, [1] CFG, [2] JUHTYREWQ, [3] K, [4] LO ),
Array [1]
Array ( [0] VFG, [1] yhtredw, [2] koeutydjwiq, [3] bg, [4] hytefs, [5] M),
Array [2]
Array ( [0] BHTWQ, [1] BH, [2] NJUy))
Equally, I have a mySQL table containing the following values
myTable
AQWER
CFG
JUHTYREWQ_K_LO
VFG
yhtredw
bg_hytefs
BHTWQ
BH_ NJUy
Desired outcome
I am trying to test and reformulate the array based on matches in the SQL table so that it looks like this:
$a =
Array ( [0]
Array ( [0] AQWER, [1] CFG, [2] JUHTYREWQ_K_LO ),
Array [1]
Array ( [0] VFG, [1] yhtredw, [2] koeutydjwiq, [3] bg_hytefs, [5] M),
Array [2]
Array ( [0] BHTWQ, [1] BH_NJUy))
And to still show values that have not been found (e.g. koeutydjwiq)
What I have tried so far:
I received great help in helping me manipulate $a so as to be able to test 1 string, 2 strings and 3 strings combinations.
However my code does not successfully pick up string combinations (that are definitely there in both the Array and the Table) and as a result, does not reformat the original array $a and I can't quite figure out why.
In fact more specifically, when calling the print_r($para) in the IF statements, I get .........
Here is my code:
foreach ($a as $val) {
for ($i=0; $i<count($val); $i++) {
// A_B_C
if (isset($val[$i+2])) {
$exagon = array();
$exagon = $val[$i] . '_' . $val[$i+1] . '_' . $val[$i+2];
$conn = mysqli_connect("localhost:8889","root","root","myDB");
$query = "SELECT * FROM `myTable` WHERE LIST = '".$exagon."'";
$para = array(
);
$result = mysqli_query($conn, $query);
//echo $result;
while($row = mysqli_fetch_array($result)) {
$para[] = array($row['LIST']);
}
if (isset($para) && !empty($para)) {
print_r($para);
array_splice($a, $i, $i+2, $para);
$i=$i+2;
}
else {
unset($para);
unset($exagon);
}
}
// A_B
elseif (isset($val[$i+1])) {
$exagon = array();
$exagon = $val[$i] . '_' . $val[$i+1];
$conn = mysqli_connect("localhost:8889","root","root","myDB");
$query = "SELECT * FROM `myTable` WHERE LIST = '".$exagon."'";
$para = array(
);
$result = mysqli_query($conn, $query);
while($row = mysqli_fetch_array($result)) {
$para[] = array($row['LIST']);
}
if (isset($para) && !empty($para)) {
print_r($para);
array_splice($a, $i, $i+1, $para);
$i=$i+1;
}
else {
unset($para);
}
}
// A
else {
echo $val[$i];
}
}
}
Admittedly, through research, I have found posts and manuals to guide me on using array_splice and calling out variables in a SQL query but it is quite possible multiple errors are present in this code
Dynamic Tables in PHP
The following code works fine and it gives the required output:
<a target="_blank" href="' . $property_post_link . '" style="color:#f8953a;font-size:18px;margin-left:0px;text-decoration:none"><b>' . $property_name . '</b></a>
<div style="color:#f8953a;font-style:italic">
<!--- <span style="font-weight:bold;">Transaction Type: </span>' . $transaction_type . ' --->
<!--- <span style="font-weight:bold;padding-left:5px;">Category: </span>' . $newprop_category . ' --->
</div>
<div style="color:#f8953a;font-style:italic">
<span style="font-weight:bold">Property Type: </span>' . $proper_type . '
<span style="font-weight:bold;padding-right:5px;">BHK: </span>' . $bhk . '
<span style="font-weight:bold;padding-left:5px;">Status: </span>' . $status . '
</div>
<div style="color:#f8953a;font-style:italic">
<span style="font-weight:bold">Location: </span>' . $loc . '
<span style="font-weight:bold;padding-right:5px;">Building Name: </span>' . $buildingname . '
<span style="font-weight:bold;padding-left:5px;">No. of Floor: </span>' . $no_floor . '
</div>
<div style="color:#f8953a;font-style:italic">
<span style="font-weight:bold">Carpet Area: </span>' . $carpet_area . '
<span style="font-weight:bold;padding-right:5px;">Built up Area: </span>' . $buildup_area . '
<span style="font-weight:bold;padding-left:5px;">Price: </span>' . $price_options_select . '
</div>
<div style="color:#f8953a;font-style:italic">
<span style="font-weight:bold">Contact Person: </span>' . $contactperson . '
</div>
But I need to get this in a table. I am using the following code:
echo "<table border='1'>
<tr><th>Building</th><th>Location</th><th>BHKs</th><th>Built up Area</th><th>Furnishing</th><th>Quote</th><th>Advance Rent</th><th>Deposit</th><th>Parking</th></tr>";
foreach ($the_rows as $the_row) {
echo "<tr><td>" . $the_row->buildingname . "</td>";
echo "<td>" . $the_row->loc . "</td>";
echo "<td>" . $the_row->bhk . "</td>";
echo "<td>" . $the_row->buildup_area . "</td>";
echo "<td>" . $the_row->interior_options_select . "</td>";
echo "<td>" . $the_row->price . "</td>";
echo "<td>" . $the_row->advrent . "</td>";
echo "<td>" . $the_row->deposit . "</td>";
echo "<td>" . $the_row->car_parking . "</td></tr>";
}
echo "</table>";
But this is not fetching the results from the database. Any help is appreciated.
How to insert values to random generated rows
To insert something to my mysql table i have to use this sql command:
INSERT INTO `votes` (`voteId`, `vote`, `pageId`, `userId`) VALUES (NULL, '5', '1', '1');
Right now I have about 30000 pageId. Let's say I would like to add to every single pageId from 5 to 25 votes. Every vote will be 1 or 5. But there would be from 50% to 100% change for vote of 5.
Is it possible to do? I tried to first use text spinner to generate votes from 1 to 5 but it is not efficient to do with such a big about of data.
What do you do in SQL Server to CREATE OR ALTER?
The year is 2009 and SQL Server does not have CREATE OR ALTER/REPLACE. This is what I do instead.
IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'SynchronizeRemoteCatalog' AND ROUTINE_SCHEMA = 'dbo' AND ROUTINE_TYPE = 'PROCEDURE')
EXEC ('DROP PROCEDURE dbo.SynchronizeRemoteCatalog')
CREATE PROCEDURE dbo.SynchronizeRemoteCatalog
AS BEGIN
-- body
END
For triggers, you have to lean on the proprietary system views.
Is this the most accepted convention in the meantime?
EDIT: As n8wrl suggested, the official word suggests that this feature is not a high priority. Hence the question.
Issue with NULL value in nested case
Long story short i need to return a varchar value if stored value is null but eithers bring me a NULL value or varchar value here is the code.
in the table i have some fields null and some with data
CASE WHEN DS.DBIRTH+DS.MBIRTH+DS.YBIRTH =
'' THEN CONVERT(VARCHAR(10),PT.CLIENTDBIRTHDATE,111)
ELSE 'INPUT DATE OF BIRTH'
CASE WHEN PT.CLIENTBIRTHDATE IS NULL THEN ''
ELSE '1800-01-01'
END
END AS BFIELD
this brings me something like this
NULL
1917/05/02
NULL
1923/02/02
1967/01/05
NULL
but i need something like this
01/01/1800
1917/05/02
01/01/1800
1923/02/02
1967/01/05
01/01/1800
sorry for the ultra noob question
Select rows which are not present in other table
I've got two tables:
login_log :: ip | etc.
ip_location :: ip | location | hostname | etc.
I want to get every IP address from login_log which doesn't have a row in ip_location.
I tried this query but it doesn't work:
SELECT login_log.ip
FROM login_log
WHERE NOT EXIST (SELECT ip_location.ip
FROM ip_location
WHERE login_log.ip = ip_location.ip)
ERROR: syntax error at or near "SELECT" LINE 3: WHERE NOT EXIST (SELECT ip_location.ip`
I'm also wondering if this query (with adjustments to make it work) is the best performing query for this purpose.
What is the SQL equivalent of COUNTIF?
I am trying to count the number of times a specific filter is used given its number of occurrences in a URL parameter. I have the URL parameter as a column. Here is a sample row result:
"lat"=>"28.5383355", "lng"=>"-81.37923649999999", "near"=>"Orlando, FL", "end_period"=>"05/08/2016", "place_input"=>"Orlando, Florida", "capacity_max"=>"100", "capacity_min"=>"7", "package_type"=>"bareboat", "start_period"=>"05/08/2016"
How would I go about counting the number of occurrences of e.g "lat"? I tried using a wildcard, but SQL returned the following error message:
operator does not exist: hstore ~~ unknown
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 50
Hope somebody is able to help me out.
phpMyAdmin issue with specific table cannot edit rows errors with blank where statement
Subject pretty much says it all. I have as specific table that PMA just will absolutely not let me edit the rows. I cannot use the inline table editor and and I cannot click the "edit" link for any row.
PMA barfs with a bad query error due to the fact that for one, the "where_clause" parameter is empty in the link code, thus causing it to run some query that looks like this: SELECT * FROM database.table WHERE .... WHERE WHAT?
I have no clue why PMA does not create the edit links correctly. There is most certainly and id column that is unique and set as primary.
I'm using version 4.5.4 on a windows easyphp16.1 set up.
I have tried analyzing, checking, repairing the table with no results.
Does anyone have any idea what the heck would be causing something like this?
chart.js data from mysql database showing no height on y- axis
i use chart.js to visualize data from mysql database, i read data from mysql database using php, i use json_encode to echo the results i want to display from the sql queries and then recieve it using ajax that all worked well for me but the problem is when i come to visualize the data i got no height for the y axis value for example if i have labels ("hi","hello","what") on the x axis coming from database and on y-axis i got values ("2.6","1.5","100") and also retrieved from the database i came with only output of three points on the x-axis regardless of the value of y-axis "i mean even the value 100 is on the x axis " and i have no lines connecting the three points but when i stop by the mouse on one of the points i got information about the point coordinates thnx in advance output result
MySQL Truncating of result when using Group_Concat and Concat
I have the following SQL (I have removed some of the selesct fi:
SELECT node_revisions.title AS 'Task',
node_revisions.body AS 'Description',
Date_format(field_due_date_value, '%e/%c/%Y') AS 'Due Date',
users.name AS 'User Name',
(SELECT GROUP_CONCAT(Concat(CHAR(10),Concat_ws( ' - ', name, From_unixtime( TIMESTAMP,
'%e/%c/%Y' )),CHAR(10),COMMENT))
FROM comments
WHERE comments.nid = content_type_task.nid) AS 'Comments'
FROM content_type_task
INNER JOIN users
ON content_type_task.field_assigned_to_uid = users.uid
INNER JOIN node_revisions
ON content_type_task.vid = node_revisions.vid
ORDER BY content_type_task.nid DESC
This pulls back all my tasks and all comments associated with a task. The problem I am having is that the comments field; created using the *GROUP_CONCAT*, is truncating the output. I don't know why and I don't know how to overcome this. (It looks to be at 341ish chars)
Receiving a warning - Notice: Undefined index [duplicate]
This question already has an answer here:
I am just trying to print a few results for testing purposes but it generates the complete code instead. I am not sure how to fix it.
<?php
include "config.php";
function getfinalsp($gid, $lv) {
$ids = mysql_query("select * from actstatus where regid = '$gid'");
$row = mysql_fetch_array($ids);
$val = $row[$lv];
$fv = $row['regid'];
if($fv == $gid && $val != 'Yes') {
$gs = mysql_query("select * from register where username = '$gid'");
$grow = mysql_fetch_array($gs);
$gval = $grow['sid'];
getfinalsp($gval, $lv);
}
return $val;
}
$sp = "sam";
$vl = "123";
$act = getfinalsp($sp, $vl);
echo $act;
?>
In the above code I passed $sp, $sl variables for mysql query but I get this error:
Notice: Undefined index: 123 in C:xampphtdocsabcsam2.php on line 16
How to avoid overcomplexity on many to many relations using Eloquent?
So here is my current Database Project. General idea of relations:
- User can make many Points, Hints and Games, but all of them belong to one User.
- Points can have many Hints while Hint can belong to only one Point.
- Game can have many Hints(on image its Games_Points table, but idea is one game can use set of unique Points).
- The problematic stuff starts on Game relation with Users. Game can be created by one User however multiple Users should be able to join Game. My approach was to create many to many tables - Users_Games (its job was for User to show statistic in games he is participating and for Game to know which User is taking part in it)
- there is Games_Points_Users table for checking if User taking part in Game has reached a Point.
For additional info for last 3 mentioned tables (Users_Games, Games_Points, Games_Points_Users) I am using composite keys as primary key.
I have feeling that this design logic is flawed. Do I need to normalize my database a little more? Will I be able to make those relations work with Eloquent? Maybe I should create additional table Players which will be subtype of Users?
Join Tables Based on Time and ID
I have two tables of time series data that I am trying to query and don't know how to properly do it.
The first table is time series data of device measurements. Each device is associated with a source and the data contains an hourly measurement. In this example there are 5 devices (101-105) with data for 5 days (June 1-5).
device_id date_time source_id meas
101 2016-06-01 00:00 ABC 105
101 2016-06-01 01:00 ABC 102
101 2016-06-01 02:00 ABC 103
...
101 2016-06-05 23:00 ABC 107
102 2016-06-01 00:00 XYZ 102
...
105 2016-06-05 23:00 XYZ 104
The second table is time series data of source measurements. Each source has three hourly measurements (meas_1, meas_2 and meas_3).
source_id date_time meas_1 meas_2 meas_3
ABC 2016-06-01 00:00 100 101 102
ABC 2016-06-01 01:00 99 100 105
ABC 2016-06-01 02:00 104 108 109
...
ABC 2016-06-05 23:00 102 109 102
XYZ 2016-06-01 00:00 105 106 103
...
XYZ 2016-06-05 23:00 103 105 101
I am looking for a query to get the data for a specified date range that grabs the device's measurements and its associated source's measurements. This example is the result for querying for device 101 from June 2-4.
device_id date_time source_id d.meas s.meas_1 s.meas_2 s.meas_3
101 2016-06-02 00:00 ABC 105 100 101 102
101 2016-06-02 01:00 ABC 102 99 100 105
101 2016-06-02 02:00 ABC 103 104 108 109
...
101 2016-06-04 23:00 ABC 107 102 109 102
The actual data set could get large with lets say 100,000 devices and 90 days of hourly measurements. So any help on properly indexing the tables would be appreciated. I'm using MySQL.
SQL: performantly find the percent overlap between two foreign key child tables
Assume I have the following tables/fields:
CREATE TABLE tbl_projects (
prjc_id int PRIMARY KEY
)
CREATE TABLE tbl_project_requirements (
preq_prjc_id int -- Foreign key to tbl_projects
preq_type_id int -- A standardized requirement category
)
Given a specific project, I would like to find other projects that have nearly similar requirement categories... or let's say at least a 75% overlap on their requirements.
I could do the following:
DECLARE @prjc_id int = 1
CREATE TABLE #project_reqs (req_type_id int)
INSERT INTO #project_reqs
SELECT preq_req_type_id
FROM tbl_project_requirements
WHERE preq_prjc_id = @prjc_id
SELECT prjc_id
FROM tbl_projects
CROSS APPLY (
SELECT CASE
WHEN COUNT(*) = 0 THEN 0.0
ELSE COALESCE(SUM(CASE WHEN type_id = prjc_type_id THEN 1.0 ELSE 0.0 END), 0.0)
/ CONVERT(float, COUNT(*))
END AS similarity
FROM #project_reqs
FULL OUTER JOIN tbl_project_requirements
ON preq_type_id = type_id
WHERE preq_prjc_id = prjc_id
) reqs
WHERE prjc_id != @prjc_id
AND similarity >= 0.75
In the above, I'm dividing the matched requirement categories by the total distinct requirement categories between each two projects to get the % overlap.
While this works, I sense code smells, and don't think this will scale very well. Is there any sort of method that exists to performantly calculate overlap of child records between two items? Maybe some sort of partial hash matching or...?
Based on variables return columns, but show blank values and don't use conditions
This is a bare bones strip of my actual code, but I would like to undestand how to do this so I can apply it throughout. I have code like this:
declare
use_t3 varchar2(1): = 'N'; //Y or N
use_t4 varchar2(1): = 'N'; //Y or N
begin
INSERT INTO MY_BIG_TABLE
SELECT
t3.qid AS "QID",
(SELECT t5.flag FROM t5 WHERE(t5.qid = t4.qid)) AS "FLAG"
FROM
t1 p,
t2 pm,
t3 pms,
t4 pmo
WHERE
t1.id = t2.qid
AND t3.day = 'Monday'
AND t2.id = t4.pmd_id(+)
AND t4.date IS NULL
end;
Now I have two variables:
use_t3
use_t4
If use_t3 and use_t4 are both equal to 'Y' (which means Yes, use these tables) then the query should be run as is shown exactly as both tables should be used (only either use_t1 or use_t2 can be equal to 'N', which means there will always be one which is qual to 'Y').
If use_t3 = 'N' and use_t4 = 'Y' then I would like the t3.qid column to still show but to return null, and for the condition:
t3.day = 'Monday'
to not be used.
If use_t3 = 'Y' and use_t4 = 'N' then I would like the "FLAG" column to return blank values, and the conditions:
t2.id = t4.pmd_id(+)
AND t4.date IS NULL
to not be used.
I hope this makes sense.
Cheers
Mysql truncates concatenated result of a GROUP_CONCAT Function
I've created a view which uses GROUP_CONCAT to concatenate results from a query on products column with data type of 'varchar(7) utf8_general_ci' in a column named concat_products.
The problem is that mysql truncates value of concat_products column.
phpMyAdmin says the data type of concat_products column is varchar(341) utf8_bin
table products:
CREATE TABLE `products`(
`productId` tinyint(2) unsigned NOT NULL AUTO_INCREMENT,
`product` varchar(7) COLLATE utf8_general_ci NOT NULL,
`price` mediumint(5) unsigned NOT NULL,
PRIMARY KEY (`productId`))
ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci
concat_products_vw View:
CREATE VIEW concat_products_vw AS
SELECT
`userId`,
GROUP_CONCAT(CONCAT_WS('_', `product`, `productId`, `price`)
ORDER BY `productId` ASC SEPARATOR '*') AS concat_products
FROM
`users`
LEFT JOIN `products`
ON `users`.`accountBalance` >= `product`.`price`
GROUP BY `productId`
according to mysql manual
Values in VARCHAR columns are variable-length strings
Length can be specified as a value from 1 to 255 before MySQL 4.0.2 and 0 to 255 as of MySQL 4.0.2.
edit:
Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 65,535.
Why mysql specifies more than 255 characters for varchar concat_products column?(solved!)
Why uf8_bin instead of utf8_general_ci?
Is it possible to change the data type of a column in a view for example in my case to text for concat_products column?
If not what can i do to prevent mysql from truncating concat_products column?
Best approach to creating a matrix of missingness from multiple tables in a SQL database
I have a SQL database with approximately 9 million observations, divided into 5 tables. I have established ODBC connectivity in R using RODBC and have explored the data a bit. The end-goal of what I am trying to do is to create a database that people with their own research questions related to the data can query the database and extract relevant variables to perform their analyses. To do this, I first need to create a matrix of missingness to figure out what variables exist for which observations. The tables cover the time period from 1994-2012, though 2 tables only cover up to 2011. I envision the matrix looking something like this(see link below). I think making a matrix of missingness for each year, with each table in the database as a row, then every variable in the database as the columns, and in each cell would be the number of observations and % missing. I am open to other ideas of course. I am not sure how to go about making this happen, however, as there are different columns in each table and there are also some columns that are the same (i.e. id, name, etc). I am wondering if it is best to create such a matrix for each table first, with each year as a row, then the variables as columns, and cells containing the same information as in the end-goal matrix. I would then make this a permanent table to be manipulated using R alone. This would prevent my machine from crashing/taking forever by joining all of those observations into a massive table. Then I would still need to unite all the tables by year. Any suggestions/insight/feedback are welcomed. Thank you all in advance.
Individual year of matrix of missingness:
Rails: Find all records matching condition for any of the associated objects
I have the following Ruby on Rails entity:
Playlist:
class Playlist < ActiveRecord::Base {
:id => :integer,
:name => :string,
:created_at => :datetime,
:updated_at => :datetime,
:dimension => :string,
:title => :string,
:text => :string,
:price => :float,
:playlist_image_file_name => :string,
:playlist_image_content_type => :string,
:playlist_image_file_size => :integer,
:playlist_image_updated_at => :datetime,
:main => :boolean,
:action => :string,
:hairdresser_id => :integer
}
And Keyword:
class Keyword < ActiveRecord::Base {
:id => :integer,
:name => :string,
:created_at => :datetime,
:updated_at => :datetime,
:preselected => :boolean
}
The relation between them is very simple: basically a Playlist object can have 0 or more keywords associated. Those are the models:
class Keyword < ActiveRecord::Base
has_and_belongs_to_many :playlists
end
class Playlist < ActiveRecord::Base
has_and_belongs_to_many :keywords
has_attached_file :playlist_image, styles: {medium: "500x500", small: "200x200", thumb: "40x40"}, default_url: "/system/playlist_default.jpg"
validates_attachment_content_type :playlist_image, content_type: /Aimage/.*Z/
end
That allows me to do something like that:
Playlist.first.keywords
and retrieve all the keywords associated with the first playlist.
Now I would like to build a function to return all the playlists that have certain words as keywords and have "main" equals to true. For example all the playlist that have the keyword "Summer". I tryed with that:
Playlist.where(:main => true).map{|x| x.keywords.include? "Summer"}
But that returns only an array containing true or false depending if the related Playlist contains or not the keyword "Summer", I'm looking for something that return the whole playlist only if the array of keywords of that playlist include the word "summer". How can I achieve that?
MySQL GROUP BY acting strange, not returning expected result
I'm trying to return the row with ID 258776, here's what I've tried so far...
Without GROUP BY:
SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
INNER JOIN message AS msg ON i.message_id = msg.id
WHERE i.profile_id = 2135
AND i.is_sent = 0
AND i.is_deleted = 0
AND msg.uid = '570cc3a568402'
ORDER BY msg.updated_at DESC
) AS main #GROUP BY main.uid
= ID = =MSG = =INB = = UID = = BD = = CREATED =
258776 258776 524785 570cc3a568402 wtf 2016-06-22 11:34:29
217149 217149 438907 570cc3a568402 <br /> 2016-04-12 11:45:09
Try with GROUP BY
SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
INNER JOIN message AS msg ON i.message_id = msg.id
WHERE i.profile_id = 2135
AND i.is_sent = 0
AND i.is_deleted = 0
AND msg.uid = '570cc3a568402'
ORDER BY msg.updated_at DESC
) AS main GROUP BY main.uid
= ID = =MSG = =INB = = UID = = BD = = CREATED =
217149 217149 438907 570cc3a568402 <br /> 2016-04-12 11:45:09
Switch to ASC but GROUP BY gives same results ?
SELECT main.id, main.message_id, main.inbox_id, main.uid, main.body, main.created_at FROM (
SELECT i.id as inbox_id, i.message_id, msg.* FROM inbox AS i
INNER JOIN message AS msg ON i.message_id = msg.id
WHERE i.profile_id = 2135
AND i.is_sent = 0
AND i.is_deleted = 0
AND msg.uid = '570cc3a568402'
ORDER BY msg.updated_at ASC
) AS main GROUP BY main.uid
= ID = =MSG = =INB = = UID = = BD = = CREATED =
217149 217149 438907 570cc3a568402 <br /> 2016-04-12 11:45:09
I presume it should work fine if I didn't need to use an INNER join ? :(
Join tables with SUM issue in MYSQL
I have always had trouble getting SUMs on join tables, there is always an issue, I can get the results I need by running two queries, I am wondering if this two queries can be combine to make one join query, here is the queries I have and my attempt to join the query
Query 1
SELECT last_name, first_name, DATE_FORMAT( (mil_date), '%m/%d/%y' ) AS dates,
SUM( drive_time ) MINUTES FROM bhds_mileage LEFT JOIN bhds_teachers i
ON i.ds_id = bhds_mileage.ds_id
WHERE mil_date BETWEEN '2016-04-11' AND '2016-04-30'
AND bhds_mileage.ds_id =5
GROUP BY CONCAT( YEAR( mil_date ) , '/', WEEK( mil_date ) ) ,
bhds_mileage.ds_id
ORDER BY last_name ASC , dates ASC
the output in minutes is 271, 281, 279
Query 2
SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,
SUM(tm_hours) total FROM bhds_timecard LEFT JOIN bhds_teachers i
ON i.ds_id = bhds_timecard.ds_id
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id
ORDER BY last_name ASC, dates ASC
The output here is 33.00, 36.00, 26.75
Now my attempt to join the query
SELECT last_name, first_name, DATE_FORMAT((tm_date), '%m/%d/%y') AS dates,
SUM(tm_hours) total, SUM( drive_time ) MINUTES FROM bhds_timecard
LEFT JOIN bhds_teachers i ON i.ds_id = bhds_timecard.ds_id
LEFT JOIN bhds_mileage ON DATE_FORMAT((bhds_timecard.tm_date), '%m/%d/%y') =
DATE_FORMAT((bhds_mileage.mil_date), '%m/%d/%y') AND bhds_timecard.ds_id = bhds_mileage.ds_id
WHERE tm_date BETWEEN '2016-04-11' AND '2016-04-30' AND bhds_timecard.ds_id = 5
GROUP BY CONCAT(YEAR(tm_date), '/', WEEK(tm_date)), bhds_timecard.ds_id
parenthesis is what is expected
this outputs 1044 (271), 1086 (281), 1215 (279)
mercredi 22 juin 2016
MySQLdb is not filling rows in DB with data, but feildes are auto-incremented
I am using MySQlDb to connect and populate my DB with a python script. Data from a BGP stream (dump) is going in to the DB. But when I try to execute(insert data) with SQL on line 65 in the bottom of the code, the DB is not affected, besides that the row does auto-increment on one of it’s fields. Is this a encoding issue? I am using utf-8 in python and utf-8, utf8_swedish_ci in MySQL. Code I am using:
#!/usr/bin/env python
# -*- coding: utf-8 -*-
from _pybgpstream import BGPStream, BGPRecord, BGPElem
from collections import defaultdict
import time
import datetime
import os
import MySQLdb
db = MySQLdb.connect(user="bgpstream", host="localhost", passwd="Bgpstream9", db="bgpstream_copy")
db_cursor = db.cursor()
# Create a new bgpstream instance and a reusable bgprecord instance
stream = BGPStream()
rec = BGPRecord()
# Consider Route Views origon only
collector_name = 'rrc11'
stream.add_filter('collector',collector_name) #maybe we want route-views4?
t_end = int(time.time()) #current time now
t_start = t_end-3600 #the time interval (duration) we are getting from collecor, e.i 60*60 = 3600s = 1 hour
stream.add_interval_filter(t_start,t_end)
print "Total duration " + str(t_end-t_start) + " sec"
# Start the stream
stream.start()
### Insert loop ###
# This loop insert new records and tries not to over count the records
# Get next record:
while(stream.get_next_record(rec)):
# Print the record information only if it is not a valid record
if rec.status != "valid":
print rec.project, rec.collector, rec.type, rec.time, rec.status
else:
# Skip if rib
if rec.type == "rib":
continue
#get affected rows from insert
affected_rows = db.affected_rows()
# Skip if dulpicate record
if affected_rows <= 0:
continue
# Extract insert id of last inserted bgp record
last_record_id = db.insert_id()
print last_record_id
# Traverse elements
elem = rec.get_next_elem()
while(elem):
print last_record_id
## Dette bør kaste en exeption
if elem == None:
continue
# Insert element
db_cursor.execute(
"""INSERT INTO bgp_elements
(record_id_owner, element_time, peer_address, peer_asn)
VALUES
(
'"""+str(last_record_id)+"""',
'"""+str(elem.time)+"""',
'"""+str(elem.peer_address)+"""',
'"""+str(elem.peer_asn)+"""'
)
""")
elem = rec.get_next_elem()
PHP MySQL Loop/Foreach
I have researched for quite a while on how to get this done through other questions, but I cannot quite get a hold of it. This link seemed most similar, but I could not implement it correctly, perhaps it can help someone on this question? MySQL while loop query inside other while loop query
I am looking to display a table that uses info from our DB to display a user's parlays, games within parlays and associated wagers and odds. I would like it to look like the following:
Parlay ID Home Team Away Team User Wager Odds
Team A Team B
1 Team C name of teams.id = 4 Ridge Robinson 1000 2.0
name of teams.id = 12 name of teams.id = 16
-------------------------------------------------------------------------------------- (<tr> border)
Team C name of teams.id = 4
2 name of teams.id = x name of teams.id = x
-------------------------------------------------------------------------------------- (<tr> border)
I can easily create this table showing all columns for each item, but the problem is, each item then becomes its own row. As you can see in my example, there is a parlay showing per row...that means there is only one parlay id, one wager, one user, one odds, etc per PARLAY. The only multiple event is the number of games per parlay that can vary between 1 or greater.
I created this table using a while loop to loop through each parlay, but unfortunately, this table shows information on each row for each column, rather than what I show in my picture.
while($row = $result->fetch_array()) {
$output = '<tr>';
$output .= '<td>'.$row['Parlay ID'].'</td>';
$output .= '<td>'.$row['Home Team'].'</td>';
$output .= '<td>'.$row['Away Team'].'</td>';
$output .= '<td>'.$row['User'].'</td>';
$output .= '<td>'.$row['Wager'].'</td>';
$output .= '<td>'.$row['Odds'].'</td>';
$output = '</tr>';
}
I believe that I will have to create some secondary loop inside the 'Home Team' and 'Away Team' $output's...does anyone know how I can get this to work where the loop will show all games in my PARLAYGAMES table that match the Parlay ID given by each row, in a way that can show it like my example table above?
I am thinking something similar to below. I know the format is terrible(!), but I am not sure how to write it correctly so I am trying to convey my idea/logic.
$output .= '<td>'while(*PARLAY TABLE id = $row['PARLAYGAMES.parlayid']) { echo PARLAYGAMES.gameid }'</td>';
For reference, my DB table has games listed as with the home and away teams a reference to the id of the teams table.
GAMES
id Home Team Away Team
1 10 5
I then have a
PARLAY TABLE
id userid wager odds
1 44 1000 2.0
Finally, I have a
PARLAYGAMES TABLE
id parlayid gameid
1 1 1
2 1 4
Thanks for any assistance!
EDIT
I worked to keep my example as simple as possible by not showing all table columns, etc so we can get the logic/code part down, but I am going to show my whole query as it seems to make the most sense to do so:
SELECT
u.first_name AS 'User First Name',
u.last_name AS 'User Last Name',
u.id AS 'User ID',
ht.name AS 'Home Team',
away.name AS 'Away Team',
p.wager AS 'Wager',
p.odds AS 'Odds',
pg.parlayid AS 'Parlay ID',
pg.betinfo AS 'Bet Info',
pg.gameid AS 'Game ID',
g.date AS 'Game Date',
b.betting_site_name AS 'Betting Site'
FROM parlays p
JOIN parlaygames pg ON p.id = pg.parlayid
JOIN games g ON pg.gameid = g.id
JOIN teams ht ON g.home_team = ht.id
JOIN teams away ON g.away_team = away.id
JOIN users u ON u.id = p.userid
JOIN bonuses b ON p.bettingsiteid = b.id
ORDER BY
pg.parlayid,
pg.gameid
Inscription à :
Articles (Atom)