[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

insert query not working on server only on localhost

Posted on 2010-09-20
8
Medium Priority
?
349 Views
Last Modified: 2012-05-10
Hi,

I have a insert query that does not seem to work.

 

I am creating a entry into matches to get match_id and then use match_id to populate the rest of the info into the other table.

 

It works on localhost, but not on my server. It gives me this:
Warning:  mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in /home/site/create-fixtures.php on line 39

line 39 is: while($match = mysql_fetch_assoc($result))
$season_id = date('Y');

 

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}

Open in new window

0
Comment
Question by:cataleptic_state
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Expert Comment

by:orsobruno
ID: 33714613
try to correct the query

SELECT h.team_id as hometeam, a.team_id as awayteam FROM team AS h, team AS a WHERE h.team_id <> a.team_id
0
 
LVL 2

Expert Comment

by:rameshfromind
ID: 33714852
I think this query will return the null value thats why getting error if there is any value available only control enter the while loop.

1. There is no data in server database thourgh the condition[h.team_id <> a.team_id].
2. But you have  the data in local server.

'SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id'

If you need any assistance let me know.
0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33716058
Hi,
the team information is in the server database.

Also on some of the other pages it says "No database selected"
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33724490
When you are running locally, are you also connecting to a local database? And then when you run it on the remote server, is the database local to the webserver? And are you updating your config file or database connection values to use the proper credentials in each environment?

You could also check to see if either the connection string is wrong once you push the code to the remote server, or the database doesn't have the proper permissions to allow connections from the remote server.
0
 
LVL 5

Expert Comment

by:innotionent
ID: 33724703
Is that all your code?
where is your mysql conection command?


$link = @mysql_connect("localhost", "username", "password") or die("<b>Could not connect to DB</b>");
	
$myquerystring = "select * from table where foo = bar";
	
if(!($result = mysql_db_query("database",$myquerystring,$link))){
	print("MySQL reports: " . mysql_error() . "\n");
		exit();
}

Open in new window

0
 
LVL 4

Author Comment

by:cataleptic_state
ID: 33724945
Hi,
This is all I have on this page (in the way of mysql):

<?php require_once('Connections/db.php'); ?>
<?php
 include "signInChk.php";
?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

$season_id = date('Y');

// Get the list of matches
$result = mysql_query('SELECT h.team_id as hometeam, a.team_id as awayteam FROM team h, team a WHERE h.team_id <> a.team_id');
while($match = mysql_fetch_assoc($result))
{
  // Create a match record and 2 matchscore records
  mysql_query("insert into matches set season_id = $season_id");
  $match_id = mysql_insert_id();
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['hometeam']}");
  mysql_query("insert into matchscores set match_id=$match_id, team={$match['awayteam']}");
}


$maxRows_news = 6;
$pageNum_news = 0;
if (isset($_GET['pageNum_news'])) {
  $pageNum_news = $_GET['pageNum_news'];
}
$startRow_news = $pageNum_news * $maxRows_news;

mysql_select_db($database_db, $db);
$query_news = "SELECT * FROM news ORDER BY news_id DESC";
$query_limit_news = sprintf("%s LIMIT %d, %d", $query_news, $startRow_news, $maxRows_news);
$news = mysql_query($query_limit_news, $db) or die(mysql_error());
$row_news = mysql_fetch_assoc($news);

if (isset($_GET['totalRows_news'])) {
  $totalRows_news = $_GET['totalRows_news'];
} else {
  $all_news = mysql_query($query_news);
  $totalRows_news = mysql_num_rows($all_news);
}
$totalPages_news = ceil($totalRows_news/$maxRows_news)-1;

mysql_select_db($database_db, $db);
$query_enable_login = "SELECT disable_login FROM season";
$enable_login = mysql_query($query_enable_login, $db) or die(mysql_error());
$row_enable_login = mysql_fetch_assoc($enable_login);
$totalRows_enable_login = mysql_num_rows($enable_login);

// Initialize the Alternate Color counter
$ac_sw1 = 0;
?>

Open in new window

0
 
LVL 5

Accepted Solution

by:
innotionent earned 2000 total points
ID: 33725034
i see you have the database variables in the code. but where are the actual values in your script?

that no database selected error you are getting means exactly that.

you haven't specified a database for it to query.

$database_db and $db must have values in them somewhere. just make sure that you do.
0
 
LVL 4

Expert Comment

by:chrisbloom7
ID: 33725499
Somewhere in Connections/db.php there should be a line that calls mysql_connect(). If not, then that is part of your problem. If it's there, then you need to make sure you are not getting a connection error remove any "@" sign before the call and refresh the page, then check for any errors displayed on the page or in your PHP error log. If the connection is failing any error message should give you an indication of why. If you feel that you need to post your Connections/db.php file be sure to redact any sensitive information (e.g. username, password, database URL) first.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month19 days, 23 hours left to enroll

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question